CODEWITHSUNDEEP
sqlsql-server
Sugandha Sharma
Sugandha Sharma

Reputation: 65

Lag function for dates in SQL

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

enter image description here

Upvotes: 2

Views: 18158

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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.

Upvotes: 3

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65363

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.

Demo

Upvotes: 1

Related Questions