Search code examples
sqloracleoracle11g

LAG Function over dates

select to_date(to_char(order_date, 'yyyymmdd'), 'yyyymmdd') -
         lag(to_date(to_char(order_date, 'yyyymmdd'), 'yyyymmdd'), 1, 0)
         over (order by order_id)
from orders

I want to find the difference in days between two dates, but when I apply lag function to the date it returns NUMBER value -

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Tried to do some conversions with TO_CHAR, CAST, but it doesn't help much. Any help is appreciated!

Solution

  • Is order_date a date? If so, doing a to_char on it just to do a to_date on it again is pointless (if you are just trying to set the time component of the date to midnight, use trunc(order_date)). Assuming it is a date

    order_date - lag(order_date) over (order by order_id)

    should be valid syntax.

    But it seems unlikely that you want to know the number of days between successive orders (which will almost certainly be a small fraction of a day) rather than, say, the number of days between successive orders for the same customer which would likely involve having a partition by clause in your analytic function.