locked
Convert Bigint (18 Character) into Datetime2 and Datetime2 to Bigint RRS feed

  • Question

  • Dear Experts, 

    I have a requirement to convert a Bigint to Datetime2 and datetime2 to bigint. 

    I have been using the below

    @bigint = 637217472419213684 

    select cast((637217472419213684 - 599266080000000000)/ 864000000000 as datetime) 

    I have been getting the correct date but not time. Any help would be appreciated. 

    Also, can I have vice versa also ? 

    Thanks 

    Momen


    Thank you... MOMEN

    Monday, April 6, 2020 5:28 AM

All replies

  • I have been getting the correct date but not time. 

    I am getting 2020-04-06 05:20:41.920; which value do you expect? What's the Formular for the conversion?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, April 6, 2020 8:16 AM
  • I need the TickTime (bigint) value to be converted to UTC datetime. Currently I figured out the way llike 

    @days = @ticktimevalue * Power(10.00000000000), -7 ) /60/60/24 

    Dateadd(d, cast(@days), cast '0001-01-01' as date) -- this gives date  (as expected)

    Cast (@days- Floor(@days) as datetime) -- this gives date  with timestamp  (NOT AS EXPECTED)

    Not sure, the formula that i use is correct for this conversion. May be the Computation is different for a leap year ? 


    Thank you... MOMEN

    Monday, April 6, 2020 10:44 AM