Geek asks and answers

SQL convert INT to datetime

I have 1323648000 which is int (10). I need to convert it to a date format. Something like dd / hh / yy hh: mm: ss. I tried a few examples here, but I can't get it to work. I tried to use it as varchar (10) and convert, but nothing. Excel also outputs ########. Is this number correct?

SELECT
engine4_openid_statusstats.openidstat_id,
CONVERT(datetime,CAST(engine4_openid_statusstats.openidstat_time AS varchar(10),3),
engine4_openid_services.openidservice_id,
engine4_openid_services.openidservice_name
FROM
engine4_openid_statusstats ,
engine4_openid_services
+5
source share
3 answers

It looks like a Unix-style era timestamp i.e. number of seconds since 1970.

The conversion is specific to RDBMS. With SQLITE, you would do

select datetime( 1323648000, 'unixepoch' );

and it gives 2011-12-12 00:00:00 (GMT).

Check the RDBMS documentation for date and time conversion functions.

+5
source

Epoch Time (, ), 01 1970 . datetime, .

SELECT DATEADD(SS, 1323648000, '01/01/1970')
+2
TO_CHAR(column_name, 'DD/MM/YYYY hh:mm:ss')
+1

More articles:


All Articles