Convert Bigint time to datetime

  • Hi,

    How to convert bigint time to datetime (CST)

    Declare @MyBigIntTime BIGINT =1397750400000

    Thanks,

    PSB

  • What units are your bigint in? Minutes, seconds, hours, years, microseconds, months ,picoseconds?

  • Minutes

  • Also, what date and time does the value provided equate?

  • What is the base date? 1900-01-01, 1970-01-01. The basic idea is:

    DATEADD(Minute, Integer, BaseDate)

  • Unfortunately, you get an overflow error when trying to convert the BIGINT value provided to an INT value when using DATEADD.

  • Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.

  • gbritton1 (4/17/2014)


    Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.

    Okay, using what for a base? The OP still hasn't told us what the value provided represents.

  • select DATEADD(Minute, 1397750400000, '1970-00-01')

    Getting an overflow error .

    Arithmetic overflow error converting expression to data type int.

  • PSB (4/17/2014)


    select DATEADD(Minute, 1397750400000, '1970-00-01')

    Getting an overflow error .

    Arithmetic overflow error converting expression to data type int.

    Yep, told you that would happen earlier.

    Now, what date and time does 1397750400000 represent??

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

    worked for me!

  • PSB (4/17/2014)


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

    worked for me!

    So the BIGINT value was actually milliseconds since midnight 1970-01-01, not minutes. Good to know.

  • Something to consider:

    select datediff(m, '0001-01-01', '9999-12-31')

    returns 119987

    That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.

    Is 1397750400000 the minute of the Big Bang or something like that?

  • gbritton1 (4/17/2014)


    Something to consider:

    select datediff(m, '0001-01-01', '9999-12-31')

    returns 119987

    That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.

    Is 1397750400000 the minute of the Big Bang or something like that?

    the "m" is MONTH not minute, the shorthand for minute is mi or n. That's why I always spell out the full identifier: MONTH, YEAR, DAY, MINUTE, HOUR, SECOND, MILLISECOND instead of the abbreviation.

  • Sorry, you're quite right! That should be:

    declare @maxminutes bigint = datediff(day, '0001-01-01', '9999-12-31')*cast(24*60 as bigint)

    select @maxminutes

    which yields: 5258963520

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply