Search code examples
oracledatediff

In Oracle, how to calculate DateDiff and use it in WHERE

In a table, there are multiple columns, include DATE1 and DATE2. I want to calculate the date difference in DATE1 and DATE2, and use it in a WHERE clause. The date difference has to between 30 to 90 days. How to do it?

I've tried to use the function below, but no luck.

@DATEDIFF ('difference', 'date', 'date')

Solution

  • The difference between two days is the number of days between them, which can be a decimal. So, for example:

    WHERE date2 - date1 BETWEEN 60 AND 90

    Would give you rows where date2 (the later date) is 60 to 90 days later than date1.

    If you need to calculate minutes or seconds, you simply multiply the result by a constant:

    (date2 - date1)*1440 -- number of minutes
(date2 - date1)*86400 -- number of seconds