Equivalent of DATEDIFF function of the SQL Server under Oracle.

November 2016


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.


Issue


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....

Solution


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.

Related :

This document entitled « Equivalent of DATEDIFF function of the SQL Server under Oracle. » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.