Tuesday, October 28, 2008

HOW TO: Convert datetime stored in seconds to readable format

I needed to convert datetime stored in seconds to valid readable datetime, and came across this article
Apparently Unix timestamp range only from 1970 Jan 1 to 2038-01-19 at 3:14:08 AM, good to know


SELECT
scheduled_start_date, scheduled_end_date
,StartDate = DATEADD(s, scheduled_start_date, '19700101')
,EndDate = DATEADD(s, scheduled_end_date, '19700101')
FROM TABLE (nolock)


scheduled_start_date   scheduled_end_date  StartDate   EndDate
1219939200 1219942800  2008
-08-28 16:00:00.000  2008-08-28 17:00:00.000
1220108400 1220133600  2008
-08-30 15:00:00.000  2008-08-30 22:00:00.000
1221163200 1221163380  2008
-09-11 20:00:00.000  2008-09-11 20:03:00.000
1221768000 1221786000  2008
-09-18 20:00:00.000  2008-09-19 01:00:00.000
1221768000 1221786000  2008
-09-18 20:00:00.000  2008-09-19 01:00:00.000
1223413200 1223416800  2008
-10-07 21:00:00.000  2008-10-07 22:00:00.000
1225213200 1225215000  2008
-10-28 17:00:00.000  2008-10-28 17:30:00.000
1225206000 1225209600  2008
-10-28 15:00:00.000  2008-10-28 16:00:00.000
1225204200 1225206000  2008
-10-28 14:30:00.000  2008-10-28 15:00:00.000
1225377000 1225378800  2008
-10-30 14:30:00.000  2008-10-30 15:00:00.000
1225215000 1225216800  2008
-10-28 17:30:00.000  2008-10-28 18:00:00.000
1225627200 1225628100  2008
-11-02 12:00:00.000  2008-11-02 12:15:00.000

No comments:

Post a Comment