Wednesday, September 18, 2013

Convert Sql Server DateTime to Milliseconds Since 1970

watch
Photo by Rich Masso
While noodling around with HighCharts, I needed to convert dates from Sql Server's DateTime to what HighCharts needs, which is milliseconds since 1970.



My first attempt to convert the current UTC time was not successful: 
 
 
select Datediff(s, '1970-01-01', GETUTCDATE())*1000
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Since the number of milliseconds is greater than the precision of int, we have to bring up the big guns: "bigint". 
 
select cast(Datediff(s, '1970-01-01', GETUTCDATE()) AS bigint)*1000

This worked great. A good site to check the values is www.epochconverter.com.
at

6 comments:

  1. Anonymous6:07 AM

    Thanks for the tip! Saved me some time :)

    ReplyDelete
  2. Anonymous2:27 AM

    Thanks. It was really helpful.

    ReplyDelete
  3. Anonymous2:15 PM

    Why not just ms with datediff instead?

    ReplyDelete
  4. Mitch Fincher2:45 PM

    Thanks for the note. I tried using 'MS' like this,

    select cast(Datediff(MS, '1970-01-01', GETUTCDATE()) AS bigint)

    which is cleaner than multiplying by 1000, but I got this error:
    "The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart."

    If you can get it to work let me know, because it is simpler.

    -Mitch

    ReplyDelete
  5. Anonymous7:44 PM

    Try
    select Datediff_big(MS, '1970-01-01', GETUTCDATE());

    ReplyDelete
  6. Anonymous5:47 AM

    Thanks for the help, Mitch!

    ReplyDelete