CODEWITHSUNDEEP
sqlsql-server-2008sqlexception
User12345
User12345

Reputation: 53

SQL datediff function resulted in an overflow

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?

Upvotes: 1

Views: 9607

Answers (3)

Charleh
Charleh

Reputation: 14002

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

Upvotes: 2

unlimit
unlimit

Reputation: 3752

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

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.

Upvotes: 0

Related Questions