none
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