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

Hiç yorum yok:

Visual Studio 2017'de Devexpress 17.2.5 Toolbox görünmüyor

Visual Studio 2017 Toolbox'ı üzerinde Developer Express componentlerini göremiyorsanız aşağıdaki komutu çalıştırmak işini görecektir. (...