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 ?

 

Answer 1

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.

Answer 2

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

Answer 3

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

 

Answer 4

Thanks again for u r quick response i will use hour and let u know the result

 

Answer 5

Hi Konstantin Kosinsky

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

many thanks to msdn forum......

 

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

 

Answer 6

 

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

Answer 7

 

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.

 

Answer 8

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.