Clever Geek Handbook

How to calculate difference between 2 times in SQL

I have 2 columns in a table i.e. DutyHours(time(7))

and TimeSpentInOffice(time(7))

.

How can I calculate the difference between these two times?

The function datediff

returns in an hour, minute, second, or day, etc., but not on time.

+3


source to share


1 answer 


DECLARE @null time;
SET @null = '00:00:00';

SELECT DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)

Ref: Time Difference



Edit: As per the comment, if the difference between end time and start time could be negative, you need to use the operator case

as such

SELECT CASE
           WHEN DATEDIFF(SECOND, End_Time, Start_Time) <=0
           THEN DATEADD(SECOND, - DATEDIFF(SECOND, End_Time, Start_Time), @null)
           WHEN DATEDIFF(SECOND, End_Time, Start_Time) >0
           THEN DATEADD(SECOND,  DATEDIFF(SECOND, End_Time, Start_Time), @null)
       END AS TimeDifference
+4


source






More articles:


All Articles