In SQL Server, we can use the following method to return a date and time based on a given Unix timestamp.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

Example

Here’s an example of converting a Unix timestamp to a date/time value:

SELECT DATEADD(s, 1860935119, '1970-01-01');

Result:

2028-12-20 14:25:19.000

In this case, the Unix timestamp was 1860935119, which translated into a date and time of 2028-12-20 14:25:19.000.

Current Date/Time

Here’s an example that uses the Unix timestamp based on the current date/time:

SELECT DATEADD(s, DATEDIFF(s, '1970-01-01', GETUTCDATE()), '1970-01-01');

Result:

2022-04-18 00:31:46.000

Mind you, this is superfluous, because we could have just done the following:

SELECT GETUTCDATE();

Larger Unix Timestamps

When faced with a larger Unix timestamp value like the following:

SELECT DATEADD(s, 1867914562715876900, '1970-01-01');

We could get an overflow error like this:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

That’s because the Unix timestamp value is larger than what an integer can hold. This Unix timestamp contains nanosecond precision and has too many digits for an integer.

We have a couple of options for dealing with this. One option is to reduce the precision:

DECLARE @ts bigint = 1867914562715876900;
SELECT DATEADD(s, CONVERT(int, LEFT(@ts, 10)), '1970-01-01');

Result:

2029-03-11 09:09:22.000

Here, we used the LEFT() function to return just the first ten digits, as well as the CONVERT() function to return an integer.

If we don’t want to reduce the precision, we can do something like this:

DECLARE @ts bigint = 1867914562715876900;
SELECT DATEADD(
    ns, 
    @ts % 1000000000, 
    DATEADD( s, @ts / 1000000000, CAST('1970-01-01' as datetime2(7)) )
    );

Result:

2029-03-11 09:09:22.7158769