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.

Equivalent of DATEDIFF function of the SQL Server under Oracle

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.

See also

Knowledge communities.

• Forum
• Programming forum
• Network forum

This document entitled « Equivalent of DATEDIFF function of the SQL Server under Oracle. » from Kioskea (en.kioskea.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.