Monday, April 6, 2009

How to Convert Unix Epoch to Datetime in MS SQL

This article will demonstrate an easy way to convert an Integer Field that stores an UNIX Epoch number into a Human readable DATETIME value. This is very important when dealing with data imported from UNIX systems. This article will use a temp table to effectly demonstrate the use of the query funtion.

1. Example using MS SQL Server Management Studio Open a SQL Query window Using MS SQL Server Management Studio or your preferred SQL Query Tool to a database which you have access.

2. Execute the following to setup the table and data required for the deminstration;

* Create a TEMP Table in MS SQL by running the following;
* CREATE TABLE #tmp_epoch_test (unix_epoch_time INT);
* Insert a known value to test retreive later;
* INSERT INTO #tmp_epoch_test VALUES (1232648493);

3. Execute the following to return the DATETIME, human readable format;

* SELECT Dateadd(ss, unix_epoch_time,'19700101')
* FROM #tmp_epoch_test;

Returned Results should be: "2009-01-22 18:21:33.000"

4. Execute the following to remove the TEMP TABLE;

DROP TABLE #tmp_epoch_test

No comments: