Want to know how to convert a Unix timestamp to a SQL Server timestamp or vice-a-versa? This article shows you how to do it using T-SQL. Read more here.
The Unix Timestamp is the elapsed time in seconds since 1st January 1970 00:00:00, UTC time. The Unix time is also known as Unix Epoch time or POSIX. Few application users use UNIX timestamp for storing date-time values in SQL Server tables.
SQL Server Unix Timestamp
SQL Server function GETUTCDATE() returns the current UTC timestamp
You can use the DATEDIFF() function to find the difference in seconds from 1970-01-01. For example, the following T-SQL returns the current UNIX timestamp.
SELECT DATEDIFF(SECOND,'1970-01-01', GETUTCDATE()) AS 'UNIX TIMESTAMP';
Convert UNIX TimeStamp into Date-Time values
What if you have the Unix timestamp value stored in the SQL table and want to convert it into the date\timestamp. For this requirement, use the reverse mechanism by adding the number of seconds into the base date 1970-01-01.
The following query returns the timestamp by converting the Unix timestamp with the DATEADD function.
SELECT DATEADD(s, 1656994047, '1970-01-01');
