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 ?
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.
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.
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)
-- 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.