How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

Arithmetic overflow: a case for large unicorns (UNIX timestamps)

Are you dealing with a UNIX timestamp that could remind some of Y2K? For timestamps representing dates post-2038 or when experiencing an arithmetic overflow, you might want to split your calculation to overcome the over-eager SQL Server:

DECLARE @big_timestamp BIGINT = 2147483648; -- Hello, 2038!
DECLARE @seconds_in_leap_year INT = 31622400; -- No! It's not a random number, but seconds in a Leap Year. 
SELECT 
    DATEADD(SECOND, @big_timestamp % @seconds_in_leap_year, DATEADD(YEAR, @big_timestamp / @seconds_in_leap_year, '19700101')) AS datetime_converted;


Now, wave goodbye to overflow problems! 👋

Local timezone: Because Time is Local!

To correctly consider the timezone when conversing with your UNIX Timestamp, calculate the local offset and tweak your faithful timestamp:

DECLARE @Local_Time_Offset BIGINT = DATEDIFF(SECOND, GETUTCDATE(), GETDATE());
DECLARE @Unix_Timestamp BIGINT = 1620000000;
SELECT DATEADD(SECOND, (@Unix_Timestamp + @Local_Time_Offset), '19700101') AS local_date_time;


Time to enjoy a locally brewed DateTime! ☕

Reusable Function: Save the world! Reuse!

Here's a magic charm encapsulated in a user-defined function for our conversion party. Because why should you have to remember this logic over and over again, right?

CREATE FUNCTION dbo.ConvertUnixTimestampToDateTime (@UnixTimestamp BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @secondsInYear INT = 31622400; -- Leap year seconds, they're special, you know!
    RETURN DATEADD(SECOND, @UnixTimestamp % @secondsInYear, DATEADD(YEAR, @UnixTimestamp / @secondsInYear, '19700101'))
END


Behold the function! Now, getting your DateTime is as easy as this:

SELECT dbo.ConvertUnixTimestampToDateTime(your_unix_timestamp) AS readable_datetime;


In-the-trenches tips and tricks

• Sing the UTC anthem always. Use UTC dates consistently to avoid timezone inconsistencies.
• Double-check '1970-01-01' to confirm it's in YYYYMMDD format.
• Keep an eye on the results, especially with future-dated timestamps, to confirm that arithmetic overflow doesn't sneak in unnoticed.
• Modular arithmetic is your best friend for timestamps with a date beyond 2038.

DateTime to UNIX timestamp: Back to the FutUNIX

Fancy a DateTime trip back to UNIX epoch seconds? Let's spin that time machine in reverse:

SELECT DATEDIFF(SECOND, '19700101', your_datetime_field) AS back_to_unix_timestamp
FROM your_table;


Remember to mind any DateTime input formatting to nullify any conversion troubles.