How to Convert Epoch Time to Date in SQL?

To convert Epoch Time to Date in SQL use the DATEADD() function. DATEADD() function in SQL Server is used to add a time or date interval to a specified date and return the modified date.

Some features of the SQL DATEADD() function are:

• This function is used to sum up a time or a date interval to a date specified.
• This function comes under Date Functions.
• This function accepts three parameters namely interval, number, and date.
• This function can also include time in the interval section. 

Syntax

The syntax to convert Epoch Time to Date in SQL is:

DATEADD(interval, number, date)

Here we will see, how to convert epoch time to date in SQL Server using the DATEADD() function.

First, we will create an EpochDB table in a database called “Beginner“.

CREATE DATABASE Beginner;

Use the below SQL statement to switch the database context to Beginner:

USE Beginner;

Creating Table

We have the following EpochDB in our Beginner database.

CREATE TABLE EpochDOB (
Id INT,
Person VARCHAR(50), 
Dt BIGINT 
);

Add data to the table

Use the below statement to add data to the EpochDB table:

INSERT INTO EpochDOB VALUES
(1,'Anuj',848698632000),
(2,'Harsh',957532509000),
(3,'Ravi',1547455833000);

To verify the contents of the table use the below statement

SELECT * FROM EpochDOB;

SQL Query to Convert Epoch Time to Date in SQL

Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000.

Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.

SELECT *, CAST(DATEADD(SECOND, Dt/1000
 ,'1970/1/1') AS DATE) DOBDate
FROM EpochDOB;