I am in the middle of a SQL data conversion project. Amazingly, our new SQL Server database requires dates to be stored in Unix format, which is a bigint field with the number of milliseconds since 01/01/1970. Amazing, I tell you.

Here are some queries that I have found useful in this project. I have updated this article to include an alternative that does not involve functions.

convert from unix time to sql server datetime:

SELECT DATEADD(s, mycolumn / 1000, '19700101') from mytable

It would seem that we could use “ms” instead of the “s” parameter and avoid division by 1000, since the data are stored in milliseconds in mycolumn. However, that created an overflow condition. My only guess is that the dataadd function uses integer math internally instead of bigint.

Next up, how to convert the other direction:

SELECT cast(DATEDIFF(s, '19700101', cast('2012-10-10 14:05:00.000' as datetime)) as bigint) * 1000 from mytable

You’ll want to replace ‘2012-10-10 14:05:00.000’ with your own date/time string. Again we have this overflow problem if we try to get milliseconds directly in datediff, so we have to multiply by 1000.

This is all well and fine, but my data was not in the GMT timezone. So I additionally need to convert through to my timezone (CST).

SELECT cast(DATEDIFF(s, '19700101', dateadd(hh, 5, cast('2012-10-10 14:05:55.000' as datetime))) as bigint) * 1000 from mytable

As before, replace ‘2012-10-10 14:05:55.000’ with your own date/time string.

One more thing. It would be really nice if I could call these complicated queries as a function. Here’s how to create such a function.

create function timeStringToUnix
(@timestring nvarchar(30))
returns bigint
as
begin
declare @return bigint
select @return = cast(DATEDIFF(s, '19700101', dateadd(hh, 5, cast(@timestring as datetime))) as bigint) * 1000
return @return
end

This ugly query will also convert the date from the Unix time format in yourtimecolumn:

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (yourtimecolumn/1000),'1970-01-01 00:00:00')

This even uglier query will tell you how many days have elapsed since the Unixe timestamp represented in yourtimecolumn:

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (yourtimecolumn/1000),'1970-01-01 00:00:00')