SQL datediff function resulted in an overflow

Question

I am calculating TotalHours of a employee worked in office based on Intime and Outtime resultant in the form of hh.mm like 8.30.

So, I wrote below sql query :

SELECT EMPLOYEEID, sum(DateDiff(mi,isnull(In_Time,0),isnull(Out_Time,0))/60) +
sum(round(DateDiff(mi,isnull(In_Time,0),isnull(Out_Time,0))%60,2))/100.0    +
sum(round(DateDiff(ss,isnull(In_Time,0),isnull(Out_Time,0)),2))/10000.0 as
TotalHours from HR_EMPLOYEES

The above sql server query was running correctly intially, but now it is giving following exception:

java.sql.SQLException: The datediff function resulted in an overflow. The number of 
dateparts separating two date/time instances is too large. Try to use datediff with a 
less precise datepart.

Could anybody please help me to get rid off of this?

Answer 1

You may try this:

select empid,
convert(varchar(5), sum(datediff(minute, [intime], isnull([outtime], dateadd(hh, 19, DATEADD(dd, DATEDIFF(dd, 0, [intime]), 0))))) / 60) 
+ ':' +
convert(varchar(5),sum(datediff(minute, [intime], isnull([outtime], dateadd(hh, 19, DATEADD(dd, DATEDIFF(dd, 0, [intime]), 0))))) % 60)
as TotalHours
from HR_EMPLOYEES group by empid

Some thoughts:

1. Can intime ever be null? If so how and why? I am assuming intime can never be null
2. I am assuming that if outtime is null then, the employee is still working, thus the use of getdate() But it may also be the case that there was a software bug that caused the null.
3. Another strategy to handle null in outtime could be to make it the midnight of the intime day. Then this begs the question, how the next day will be handled.

I think there may be a lot of edge cases here. You will have to be careful.

EDIT: Modified outtime to 7 PM of intime day if outtime is null as per OP's comment. Used Best approach to remove time part of datetime in SQL Server