CODEWITHSUNDEEP
sqldatetimeint
rubio
rubio

Reputation: 966

SQL convert INT to datetime

I have 1323648000 which is int(10). I need to be able to convert it to a date format of some sort. Something like dd/hh/yy hh:mm:ss. I have tried to us a few examples on here but i cannot seem to get it to work. I have tried to cast it as a varchar(10) and convert but nothing. Excel also outputs ########. Is the number incorrect then?

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

Upvotes: 8

Views: 13125

Answers (3)

theglauber
theglauber

Reputation: 29595

That's looking like an Unix-style epoch-based timestamp, i.e. number of seconds since 1970.

The conversion is RDBMS-specific. With SQLITE you'd do

select datetime( 1323648000, 'unixepoch' );

and that yields 2011-12-12 00:00:00 (GMT).

Check your RDBMS documentation for date-time conversion functions.

Upvotes: 8

Matt Smucker
Matt Smucker

Reputation: 5234

If that is indeed being stored as Epoch Time (which it looks like it is), those are the number of seconds since January 01, 1970. You can convert it to a datetime by adding the seconds to this date.

SELECT DATEADD(SS, 1323648000, '01/01/1970')

Upvotes: 3

Felipe Queiroz
Felipe Queiroz

Reputation: 461

TO_CHAR(column_name, 'DD/MM/YYYY hh:mm:ss')

Upvotes: 1

Related Questions