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 :)

+4


source to share


2 answers


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

+12


source


This should work perfectly for those extended eras.



SELECT DATEADD(SECOND, 1359016610667 / 1000, '19700101 00:00')

      

+3


source







All Articles