sqlsql-serverdatediff

Calculate Time Difference Between Two Consecutive Rows

I have a table like this:

RecordID     TransDate
1            05-Oct-16 9:33:32 AM
2            05-Oct-16 9:33:37 AM
3            05-Oct-16 9:33:41 AM
4            05-Oct-16 9:33:46 AM
5            05-Oct-16 9:33:46 AM

I need to get the difference between consecutive TransDate values. I am using SQL Server 2014, and am aware of a way to use the LAG functions to do this, but I don't know how to do it.

I need this output:

RecordID     TransDate              Diff
1            05-Oct-16 9:33:32 AM   0:00:00
2            05-Oct-16 9:33:37 AM   0:00:05
3            05-Oct-16 9:33:41 AM   0:00:04
4            05-Oct-16 9:33:46 AM   0:00:05
5            05-Oct-16 9:33:46 AM   0:00:00
Solution

  • How about this:

    select recordid, transdate,
       cast( (transdate - lag(transdate) over (order by transdate)) as time) as diff
from t;

    In other words, you can subtract two datetime values and cast the result as a time. You can then format the result however you like.