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.

6 comments:

Anonymous said...

Thanks for the tip! Saved me some time :)

Anonymous said...

Thanks. It was really helpful.

Anonymous said...

Why not just ms with datediff instead?

Mitch Fincher said...

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

Anonymous said...

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

Anonymous said...

Thanks for the help, Mitch!