1

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?

Keren Caelen
  • 1,466
  • 3
  • 17
  • 38
User12345
  • 53
  • 1
  • 8
  • Any reason you can't just use `DateDiff` with the hours option instead of all these formulae? I'm also betting that since you are checking for nulls, you are getting minimum date back - and since you are asking for seconds that's what's overflowing – Charleh Jul 08 '13 at 10:07
  • You probably want to just return 0 for a null date instead – Charleh Jul 08 '13 at 10:08
  • if possible i want to return time 7:00 PM for a null date instead – User12345 Jul 08 '13 at 11:57
  • possible duplicate of [SQL Server: datediff function resulted in an overflow](http://stackoverflow.com/questions/1275208/sql-server-datediff-function-resulted-in-an-overflow) – demongolem Oct 17 '13 at 14:48

3 Answers3

2

Is it not easier to just discard the null values (returning datediff in seconds from a datetime of zero is what's probably overflowing your query) and also use 'hh' for datediff? Or are you also looking for the number of minutes (do you NEED seconds?? If this is for timesheets etc, seconds don't really matter do they?)

SELECT
    EMPLOYEEID, 
    CASE 
        WHEN In_Time IS NOT NULL AND Out_Time IS NOT NULL THEN sum(DateDiff(hh, In_Time, Out_Time))
        ELSE 0
    END as TotalHours
FROM HR_EMPLOYEES

Edit: ok for hours/minutes just use:

SELECT
    EMPLOYEEID, 
    CASE 
        WHEN In_Time IS NOT NULL AND Out_Time IS NOT NULL THEN sum(DateDiff(mi, In_Time, Out_Time)) / 60.0
        ELSE 0
    END as TotalHours
FROM HR_EMPLOYEES

This gives you hours plus a fraction of minutes (so 90 minutes = 1.5 hours)

Edit2: if you want minutes as actual minutes and not a fraction use this:

SELECT
    EMPLOYEEID, 
    CASE 
        WHEN In_Time IS NOT NULL AND Out_Time IS NOT NULL THEN 
             sum(DateDiff(hh, In_Time, Out_Time) + -- Total hours
             DateDiff(mi, In_Time, Out_Time) % 60 * .01) -- Total minutes (divided by 100 so that you can add it to the hours)
        ELSE 0
    END as TotalHours
FROM HR_EMPLOYEES
Charleh
  • 13,749
  • 3
  • 37
  • 57
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

Community
  • 1
  • 1
unlimit
  • 3,672
  • 2
  • 26
  • 34
  • yeah you are right, intime will not null ever and outtime could be null, so is there any way that i can fill outtime with constant time assume 7:00 pm in the above query. thanks – User12345 Jul 08 '13 at 11:46
  • Have made changes to the sql code, see if it works. I have not tested it. – unlimit Jul 08 '13 at 12:04
  • Hi, thanks, above query is working but for some of the employee, minutes is not displaying in total hours,like (10:22 but only displaying 10: mostly when total hours cross more than 10 hours), could please give an idea how to make it correct. – User12345 Jul 10 '13 at 04:39
  • What are the `intime` and `outtime` of these employees? – unlimit Jul 10 '13 at 05:26
  • Odd, my query works perfectly for the data you provided. Check if you have any typo in your query, look in the part after `+ ':' +`. By the way if this answer is helping you, don't forget to upvote so that it helps others. – unlimit Jul 10 '13 at 06:00
  • 'empid intime outtime totalhours remarks 1 7:38 18:00 10:22 but coming 10: 2 9:16 20:03 10:47 but coming 10: 3 9:35 19:56 10:22 but coming 10: 19:56 19:57 20:01 20:01 20:03 20:03 4 10:01 20:02 10:01 correct' – User12345 Jul 10 '13 at 06:01
0

Instead of using 0 as your default/fixed point in time, use some other constant date instead, that's closer to the values you're going to be processing - and thus less likely to produce an overflow.

0 gets implicitly converted to midnight on 01/01/1900. A better constant might be, for instance, 01/01/2000:

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

Although the more I look at this, the more I'm unsure that defaulting the date to any value even makes sense.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Agreed on the 'making sense' bit - it would appear that if they don't have an in/out time then the hours worked can't be calculated (I worked 17 thousand hours this week boss ;)) – Charleh Jul 08 '13 at 10:12
  • @Damien : thanks, error has gone now, but TotaHours is not calculating correctly for some employee, like 15:36 to 16:37, total hours is coming 1:37, could you please tell where i am doing wrong – User12345 Jul 08 '13 at 11:23