CODEWITHSUNDEEP
sql-serverdatetimeepoch
jcoke
jcoke

Reputation: 1891

Convert epoch timestamp to datetime without milliseconds?

First of all I know there are several questions out there similar to this, but none of them actually show how to display the date time format without including milliseconds.

I have this code below:

SELECT CONVERT(
   DATETIME,
   dateadd(SS,CONVERT(INT, 1354320000), CAST('1970-01-01 00:00:00' as datetime)),
   120
)

However, the code is including milliseconds but rather I need the format to be like such yyyy-mm-dd hh:mm:ss

Does anyone know an effective way to go about this?

Upvotes: 0

Views: 734

Answers (2)

Thiyagu
Thiyagu

Reputation: 1330

Try SMALL DATETIME

SELECT CAST(DATEADD(SS,CONVERT(INT, 1354320000), CAST('1970-01-01 00:00:00' AS DATETIME))AS SMALLDATETIME)

Also, By using FORMAT

SELECT FORMAT(DATEADD(SS,CONVERT(INT, 1354320000), CAST('1970-01-01 00:00:00' AS DATETIME)),'yyyy-MM-dd HH:mm:ss')

Upvotes: 1

Zhorov
Zhorov

Reputation: 29943

You may try to use an appropriate data type. As is explained in the documentation, the return value data type for DATEADD() is dynamic and it depends on the argument supplied for the date parameter.

SELECT DATEADD(
   second, 
   CONVERT(int, 1354320201), 
   CAST('1970-01-01T00:00:00' AS datetime2(0))
)

Result:

2012-12-01 00:03:21

Upvotes: 1

Related Questions