How to Convert Epoch Time to Date in SQL?

DATEADD() function in SQL Server is used to sum up a time or a date interval to a specified date then returns the modified date. There are some features of DATEADD() below: 

• 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. 

Here we will see, how to convert epoch time to date in SQL Server using the DATEADD() function. For the purpose of demonstration, we will be creating an EpochDB table in a database called “geeks“.

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks:

CREATE DATABASE geeks;

Step 2: Using the Database

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

USE geeks;

Step 3: Table Definition

We have the following EpochDB in our geeks database.

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

Step 4: Adding 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);

Step 5: To verify the contents of the table use the below statement

SELECT * FROM EpochDOB;

Step 6: Result

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;

My Personal Notes arrow_drop_up

Save