2

I'm facing a problem with an epoch timestamp with miliseconds attached.

The format of my epoch timestamp is:

1439263190,2609999

The build in dateadd function can only handle int values.

Are there any workarounds?

KR Johann

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Seits
  • 27
  • 1
  • 5
  • Try http://stackoverflow.com/questions/14716744/mssql-convert-milliseconds-since-1970-to-datetime2 or http://stackoverflow.com/questions/12524537/convert-utc-milliseconds-to-datetime-in-sql-server – Liesel May 30 '16 at 10:26
  • 1
    2609999 ms? What should be the datetime for value you have provided? – gofr1 May 30 '16 at 10:44
  • I need a datetime that looks like this "27.07.2015 21:00:00.004" need 3 decimals for the ms. – Seits Jun 20 '16 at 15:08

1 Answers1

0

Epoch Time (aka Unix Time) is the number of seconds since midnight on 1/1/1970.

To work around a DateAdd function that will only handle integer values, simply assign the value to an integer variable and use that in the call to DateAdd.

For example, this code:

declare @epoch decimal(20,7) = 1439263190.2609999
declare @epoch2 int = @epoch
select @epoch, @epoch2, DATEADD(s, @epoch2, '1970-01-01 00:00:00')

gives this result:

1439263190.2609999    1439263190    2015-08-11 03:19:50.000