Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert seconds to datetime in SQL Server

Tags:

sql

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

like image 462
Ventsislav Marinov Avatar asked Jan 27 '12 09:01

Ventsislav Marinov


People also ask

How can add seconds to datetime in SQL Server?

We can use DATEADD() function like below to add seconds to DateTime in Sql Server. DATEADD() functions first parameter value can be second or ss or s all will return the same result.

How do you convert seconds to HH mm in SQL?

MySQL SEC_TO_TIME() Function The SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds.

How do I subtract 1 second from a date in SQL?

The DATE_SUB() function allows you to subtract a certain number of date/time units from a date or datetime expression. Therefore we can use this function to return the datetime value, minus a certain number of seconds.


2 Answers

Given your example, try this:

select DATEADD(s, dateTimeInMilliseconds, '19700101')
like image 181
aF. Avatar answered Oct 19 '22 12:10

aF.


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'))
like image 2
Guffa Avatar answered Oct 19 '22 11:10

Guffa