14

I have found only one similar question but for MySQL.

I was working on a web service and had to query the database (MS SQL server). Since I couldn't get the right result I decided to test the query via a SQL client. The web service uses Hibernate to access the DB and all time values are always represented as long values (unix epoch time). In order to test it, 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');

which outputs:

1970-01-01 00:00:00.123

But, my actual data was a bit bigger

select dateadd(ms,1359016610667 ,'1970-01-01 00:00:00.0');

which outputs:

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 the exact same error. Just to be safe I tried:

select CAST (1359016610667 AS BIGINT)

which outputs:

1359016610667

I made sure that java long is equivalent to TSQL bigint - they are both 8 B long. Rereading the dateadd() documentation revealed the following:

DATEADD (datepart , number , date )
....
number
Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.

If I understand this correctly, it means that this approach can not be used to convert a unix timestamp to TSQL timestamp, which is, well, pardon my language, but just plain stupid.

My questions are:

  • is my interpretation of this situation correct?
  • is there any other one-liner to do this conversion in TSQL ?

PS
modifying the date argument ('1970-01-01 00:00:00.0') is not acceptable as solution. I'm debugging and I don't want to recalculate the miliseconds along :)

bdukes
  • 152,002
  • 23
  • 148
  • 175
linski
  • 5,046
  • 3
  • 22
  • 35

2 Answers2

21

Easy, first add whole days, then add the remaining ms. There are 86,400,000 milliseconds in a 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')
)

The result is 2013-01-24 08:36:50.667

Ben
  • 34,935
  • 6
  • 74
  • 113
13

This should work perfect for those long epoch times.

SELECT DATEADD(SECOND, 1359016610667 / 1000, '19700101 00:00')
Xander Luciano
  • 3,753
  • 7
  • 32
  • 53
Scotty
  • 139
  • 1
  • 2
  • 2
    This method of just adding seconds, rather than both milliseconds and days (as the answer from @ben) causes an arithmetic overflow error when working with dates like '31 December 2099' when SQL tries to convert the number of seconds to add into an int. – user1098928 Oct 22 '20 at 07:59
  • This results in the Y2038 bug. The `number` parameter is an integer so adding seconds like this can only go up to 2038-01-19 – Panagiotis Kanavos Sep 22 '22 at 14:43