Convert unix timezone times to TSQL datetime
I only found one similar question, but for MySQL.
I was working on a web service and had to query a database (MS SQL server). Since I couldn't get the correct result, I decided to test the query through the SQL client. The web service uses Hibernate to access the DB, and all time values are always represented as long values (Unix epoch time). To test this, I needed to convert the Unix timestamp to TSQL timestamp. This is what I came up with:
select dateadd(ms,123,'1970-01-01 00:00:00.0');
what conclusions:
1970-01-01 00:00:00.123
But my actual data was a little more
select dateadd(ms,1359016610667 ,'1970-01-01 00:00:00.0');
what conclusions:
Error code 0, SQL state 22001: Data truncation
Error code 8115, SQL state 22003: Arithmetic overflow error converting expression to data type int.
So, I tried:
select dateadd(ms,CAST (1359016610667 AS BIGINT) ,'1970-01-01 00:00:00.0');
which outputs exactly the same error. Just to be on the safe side, I tried:
select CAST (1359016610667 AS BIGINT)
what conclusions:
1359016610667
I made sure that java long is equivalent to TSQL bigint - both are long 8 B
Rereading the documentation on dateadd (), we found the following:
DATEADD (datepart, number, date)
...
number
This is an expression that can be converted to int and appended to the date part of the date. Custom variables are valid.
If I understand correctly, this means that this approach cannot be used to convert a Unix timestamp to a TSQL timestamp, which, forgive my language, is just plain silly.
My questions:
- is my interpretation of this situation correct?
- are there any other one-liners for this conversion in TSQL?
PS
changing the date ( '1970-01-01 00:00:00.0'
) argument is not acceptable as a solution. I'm debugging and don't want to recalculate milliseconds :)
source to share
Easy, add whole days first, then add the remaining ms. There are 86.4 million milliseconds per day.
declare @unixTS bigint
set @unixTS = 1359016610667
select dateadd(ms, @unixTS%(3600*24*1000),
dateadd(day, @unixTS/(3600*24*1000), '1970-01-01 00:00:00.0')
)
Result 2013-01-24 08:36:50.667
source to share