CODEWITHSUNDEEP
sqlsql-server
Ventsislav Marinov
Ventsislav Marinov

Reputation: 604

Convert seconds to datetime in SQL Server

How to convert seconds to datetime? I try this, but results are not correct: 

CONVERT(datetime, DATEADD(ms, dateTimeInSeconds, 0))

Here is an example: 1900-01-15 21:58:16.287 it's should be something like this 2010-11-02 14:56:50.997

Upvotes: 10

Views: 48677

Answers (3)

Carlos Bercero
Carlos Bercero

Reputation: 21

Informative time span string:

declare @seconds int = 93825

convert(varchar,(@seconds/86400)) + 'd:' + format(dateadd(ss,@seconds,0),'HH\h:mm\m:ss\s')

Result: 1d:02h:03m:45s

Upvotes: 2

Guffa
Guffa

Reputation: 700800

When you use the value zero for date, this is converted to 1900-01-01. Use the specific date that you have selected as epoch:

convert(datetime, dateadd(ms, dateTimeInMilliseconds, '2010-01-01'))

Note that the datetime data type doesn't have millisecond precision, the resolution is 1/300 second. If you for example have four milliseconds and convert it, you get 2010-01-01 00:00:00.003 rather than 2010-01-01 00:00:00.004. If you need to preserve the millisecond resolution, you need to use the datetime2 data type:

convert(datetime2, dateadd(ms, dateTimeInMilliseconds, cast('2010-01-01' as datetime2)))

Edit:

To use seconds instead of milliseconds, use s instead of ms in the dateadd call:

convert(datetime, dateadd(ms, dateTimeInSeconds, '1970-01-01'))

Upvotes: 2

aF.
aF.

Reputation: 66747

Given your example, try this:

select DATEADD(s, dateTimeInMilliseconds, '19700101')

Upvotes: 13

Related Questions