A few words of thanks would be greatly appreciated.

Equivalent of DATEDIFF function of the SQL Server under Oracle.

DATEDIFF is a common function in the SQL Server to find the number of days between two dates. Similarly, its counterpart, one of the most robust Relational Database Management Systems Oracle offers its own solution, although Oracle does not have the DATEDIFF function. The trunc () function can be used for calculating the number of days. Otherwise it can be multiplied by 24, 1440 or 86400 to calculate the number of days in hours, minutes & seconds respectively. Similarly, round () can be used to get the nearest day.


What's the function in Oracle that would be the equivalent to DATEDIFF in the SQL Server?

I'd like to know the difference that can exist between two dates, either in months or days or hours or minutes....


On Oracle, it is an arithmetic issue

select DATE1-DATE2 from dual 

1 = 1 day, the figure after the comma represents the fraction of days.

Use trunc () for the number of days

Otherwise, multiply the value
  • by 24 for hours
  • by 1440 for minutes
  • by 86400 for seconds

Thanks to Krysstof for this tip.

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team


Published by . Latest update on by Virginia Parsons.

This document, titled "Equivalent of DATEDIFF function of the SQL Server under Oracle.," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).