# Difference of two datetime columns caused overflow at runtime.

### Question

• I used this query to get a result

select     round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime

from customer rf

but i am getting an error  ?

"Difference of two datetime columns caused overflow at runtime."

Any idea ?

Friday, March 30, 2007 2:50 PM

### Answers

• DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
Friday, March 30, 2007 2:58 PM

### All replies

• DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
Friday, March 30, 2007 2:58 PM
• Hi,

Thank you for your quick response !

what is the solution for this problem ?

any idea to overcome this problem ?

i really need to use this sql !

thanks

Friday, March 30, 2007 3:01 PM
• Do you calculate datediff in hours? This query returns same result:

select

DateDiff(hour, convert(datetime,RECVD_DTTM),convert(datetime,ARRIVED_DTTM)) as LengthOfTime

from customer rf

Friday, March 30, 2007 3:05 PM
• Thanks again for u r quick response i will use hour and let u know the result

Friday, March 30, 2007 3:06 PM
• Hi Konstantin Kosinsky

Now my query is working perfectly thanks for your fast response to my post,

many thanks to msdn forum......

great work........................

Friday, March 30, 2007 3:27 PM
•

Try the following

select    round(cast(convert(bigint, DateDiff(n, convert(datetime,rf.RECVD_DTTM))+DatePart(s, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime

from customer rf

Wednesday, February 20, 2008 11:55 AM
•

As a recap of Jaggariah's helpful hint, if you convert it to a BigInt, you can use DateDiff with seconds for dates greater than 68 years apart.

Select DATEDIFF(s, '1/1/1970', '1/15/2070')--this returns the error

Select convert(bigint, DATEDIFF(s, '1/1/1970', '1/15/2070'))--this works fine

Addition added a few weeks later...

OK, I don't know what I was doing...

--Get it to hours, then minutes, then seconds...

Select CONVERT (bigint, DATEDIFF(hour, '1/1/1970', '1/15/2070'))*60*60--this works fine.

Wednesday, April 20, 2011 8:32 PM
• The problem with this is if you are truly looking for the correct minutes and seconds, your method still doesn't work. For example, if time A is 11:59 and time B is 12:01, the difference in minutes is 2 minutes. The difference in hours according to DATEDIFF is 1. If you then multiply that by 60 it looks as if 60 minutes had passed when in reality, only 2 minutes had passed.
Wednesday, May 18, 2011 2:35 PM
• I've been frustrated by this also for the last 15 minutes and realized that if I convert the times to bigint ms and then just subtract them, it would work.  If I have a date/time for @t1 and @t2 as BIGINT, I just say:

set @t1 = datediff(millisecond,convert(varchar,@t1,101),@t1)

set @t2 = datediff(millisecond,convert(varchar,@t2,101),@t2)

print @t2-@t1

-- of course, convert this to fractions of a day and then add days externally using datediff(day.......) to get the daily balance.  This should cover you for the day though in a pinch.

Lauren

Wednesday, July 10, 2013 5:19 PM