CODEWITHSUNDEEP
sql-serverdatetimesql-server-2012unix-timestamp
Abdul Rashid A
Abdul Rashid A

Reputation: 85

Convert Integer field Date time to Date Time

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

Upvotes: 1

Views: 1860

Answers (5)

Andomar
Andomar

Reputation: 238048

If you're on an older version of SQL Server, the format function is not available. An alternative is convert with style 105 (Italian) to get the MM-dd-yyyy part. Then you can take the last part of the 120 (odbc canonical) style to get the hh:mm:ss part:

select convert(varchar(30), dateadd(second, 1512543210, '1970-01-01'), 105) + ' ' + 
       substring(convert(varchar(30), dateadd(second, 1512543210, '1970-01-01'), 20), 12, 8)
-->
06-12-2017 06:53:30

Example at rextester.

Upvotes: 0

Partha
Partha

Reputation: 75

Try this..

select FORMAT(dateadd(SS,1512543210,'01/01/1970 00:00:00'), N'MM-dd-yyyy hh:mm:ss')

Upvotes: 0

deHaar
deHaar

Reputation: 18558

You can convert it in the database query as stated in comments and at least one other answer, but you can do it in Java, too:

Have a look at this example:

public static void main(String args[]) {
    // take the moment represented by the int from the database
    Instant instant = Instant.ofEpochSecond(1512543210);
    // create a datetime object (modern java api)
    LocalDateTime ldt = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
    // create a formatter for the pattern of your choice
    DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm:ss");
    // then print the datetime using the desired format
    System.out.println(ldt.format(dtf));
}

This outputs

06-12-2017 07:53:30

in my IDE, please check that code in yours.

Please note that you don't need to do the DATEADD operation in SQL for this, just fetch the int value from the database by something like resultSet.getInt(...) and pass it to the Instant.ofEpochSeconds(...), it will calculate the time based on "1970-01-01 00:00:00".

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272006

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.

Upvotes: 1

HereGoes
HereGoes

Reputation: 1320

You can use the CONVERT option

select CONVERT(varchar,dateadd(SS,DateTime,'01/01/1970 00:00:00') ,21) as datetime from sampledb

Upvotes: 0

Related Questions