Calculate total time between two times in sql

Question

I am using following sql to calculate time difference for records in a table and taking sum of them grouping by employeeId and login Date, But I am injecting a condition in the middle,If the LogOffTime is null I want to take the difference of current time of the day and the time employee loggedin. Note : Each employee can have multiple Logins and LogOffs in a day

ROUND(SUM(DATEDIFF(SECOND, LogInTime,   IIF(LogOffTime <> NULL,LogOffTime,GetDate()))) / 3600.0, 1) as HoursWorked

But my condition IIF(LogOffTime <> NULL,LogOffTime,GetDate()) seems incorrect here, Does somebody has any Idea, if the condition is correct.

http://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss — zedfoxus, May 29 '15 at 05:18
@zedfoxus My question is different, sorry if I didn't presented well, but I got my result. — Learner, May 29 '15 at 05:20

Learner · Accepted Answer · 2015-06-21T09:43:59.410

0

I Reversed the condition and it worked, thanks, following is the sql

ROUND(SUM(DATEDIFF(SECOND, LogInTime, 
                    IIF(LogOffTime IS NULL,GetDate(),LogOffTime)
                    )) / 3600.0, 1) as SystemHours

edited Jun 21 '15 at 09:43

answered May 29 '15 at 05:19

Learner

776
6
14
40

Calculate total time between two times in sql

1 Answers1