I am using LAG SQL function to get the difference of 2 consecutive dates in the records. THe issue is, the result it is returning is in DATE -TIME format whereas I want it either to be in Days, or hours. Attached is Output it outcome and the expected result.
Code:
select
*,
(action_date - lag(action_date) over (partition by voucher_no order by action_date)) as diff
from cte3
I would recommend using time for the last column -- although it does give you seconds as well:
SELECT DATEDIFF(day, LAG(Date) OVER (ORDER BY Date), Date ) AS diff_day,
CONVERT(TIME, Date - LAG(Date) OVER (ORDER BY Date)) as time
FROM t;
It is simple enough to convert this to just HH:MM format if you prefer:
SELECT DATEDIFF(day, LAG(Date) OVER (ORDER BY Date), Date ) AS diff_day,
CONVERT(VARCHAR(5), CONVERT(TIME, Date - LAG(Date) OVER (ORDER BY Date))) as time_hhmm
FROM t;
Here is a db<>fiddle.
I prefer this method because it is easier to format the time. In particular, the fiddle includes an example where the time is 00:01 and it is formatted as 00:01 rather than 0:1.
You can use DATEDIFF() function such as
SELECT
DATEDIFF(day, LAG(Date) OVER (ORDER BY Date), Date ) AS diff_day,
CONCAT(
DATEDIFF(minute, LAG(Date) OVER (ORDER BY Date), Date )/60,
':',
DATEDIFF(minute, LAG(Date) OVER (ORDER BY Date), Date )%60
) AS diff_hour_minute, *
FROM t
in order to get the desired difference values.
DATEDIFFrather than-.