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!
CodePudding user response:
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.