Convert Integer field Date time to Date Time

Question

I am working in SQL Server 2012. My datetime column in a data set looks like this: 1512543210. The column is in int. I want to convert it to something like dd-mm-yyyy hh:mm:ss e.g. 01-01-2019 12:12:12. But I can not do this. I am using following code:

select dateadd(SS, 1512543210, '01/01/1970 00:00:00') as datetime
from sampledb

after execute query i got this.

2017-12-06 00:00:00.0

but i want this format;

06-12-2017 00:00:00

Answer 1

You can use DATEADD to convert UNIX timestamp to DATETIME and FORMAT function to format it:

SELECT FORMAT(DATEADD(SECOND, 1512543210, '19700101'), 'dd-MM-yyyy hh:mm:ss')
-- 06-12-2017 06:53:30

Having said that, Java has DateTimeFormatter class for formatting dates. And timestamps could be used to construct date objects directly.