Friday, March 30, 2007 2:50 PM
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:58 PMDATEDIFF 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 3:01 PM
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 !
Friday, March 30, 2007 3:05 PM
Do you calculate datediff in hours? This query returns same result:
DateDiff(hour, convert(datetime,RECVD_DTTM),convert(datetime,ARRIVED_DTTM)) as LengthOfTime
Friday, March 30, 2007 3:06 PM
Thanks again for u r quick response i will use hour and let u know the result
Friday, March 30, 2007 3:27 PM
Hi Konstantin Kosinsky
Now my query is working perfectly thanks for your fast response to my post,
many thanks to msdn forum......
- Unmarked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Thursday, April 21, 2011 4:20 AM
Wednesday, February 20, 2008 11:55 AM
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, April 20, 2011 8:32 PM
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, May 18, 2011 2:35 PMThe 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.