Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Convert Bigint time to datetime RE: Convert Bigint time to datetime

  • April 21, 2014 at 6:10 am

    #1707416

    PSB (4/21/2014)

    I have a couple of 14 digit int time in my table

    SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))

    The above query throws an error :

    Arithmetic overflow error converting expression to data type int.

    Please advise how di I resolve it.

    Thanks,

    PSB

    Look at the numbers you are working with,

    INT Max Value => (2^31 - 1) = 2147483647

    61353491400000 / 1000 = 61353491400

    almost 30 times the maximum integer value, suggest you change your code to

    SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000000, convert(datetime, '1-1-1970 00:00:00')))

    and work from that.

    😎