SQL Server Datediff for an entire table

Question

I have a table called dbo.employees, I run some queries and I get the below result.

empId | timeIn   | timeOut  | timeDiff
------+----------+----------+------------
1001  | 03:49:54 | 15:43:42 | 
1017  | 06:27:10 | 13:47:40 | 
1005  | 05:49:50 | 13:42:03 |

I'd like to run an extra query to work out the timeDiff so that it turns out like the below. I'm thinking the TIMEDIFF function could do this? I'd like to calculate it in hours/minutes if possible.

empId | timeIn   | timeOut  | timeDiff
------+----------+----------+------------
1001  | 03:49:54 | 15:43:42 | 11hr, 53min
1017  | 06:27:10 | 13:47:40 | 8hr, 20min
1005  | 05:49:50 | 13:42:03 | 7hr, 52min

Thank you very much in advance.

What RDBMS system are you using? Whichever, you are probably better off returning timeDiff as (say) a number of minutes, and having the application side deal with formatting/presentation. — Philip Kelley, Nov 03 '20 at 21:42

score 0 · Accepted Answer · answered Nov 03 '20 at 21:44

Here is one option using datediff() in a lateral join and arithmetics:

select e.*, concat(d.diff / 60, 'hr, ', d.diff % 60, 'min') timediff
from dbo.employee e
cross apply (values (datediff(minute, e.timein, e.timeout))) d(diff)

Basically, the lateral join computes the difference between the two times in minutes. Then, the outer query builds the string that represents that interval as hours and minutes.

SQL Server Datediff for an entire table

1 Answers1