Equivalent of DATEDIFF function of the SQL Server under Oracle.

June 2017


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


Published by jak58. Latest update on March 15, 2012 at 07:31 AM 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 (ccm.net).