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

Question

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

Answer 1

This worked for me:

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

In case anyone wonders why 1970-01-01, This is called Epoch time.

Below is a quote from Wikipedia:

The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.

The Year 2038 Problem

Furthermore the DateAdd function takes an int for the seconds to add. So if you are trying to add more than 2147483647 seconds you'll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.

Declare @t as bigint = 4147483645

Select (@t / @oneyear) -- Years to add
Select (@t % @oneyear) -- Remaining seconds to add

-- Get Date given a timestamp @t
Declare @oneyear as int = 31622400
Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))

This will let you convert a timestamp that represents a year greater than 2038.

Answer 2

If anyone getting below error:

Arithmetic overflow error converting expression to data type int

due to unix timestamp is in bigint (instead of int), you can use this:

SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01')
FROM TABLE

Replace the hardcoded timestamp for your actual column with unix-timestamp

Source: MSSQL bigint Unix Timestamp to Datetime with milliseconds

Answer 3

try:

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;
GO

Answer 4

Like this

add the Unix (epoch) datetime to the base date in seconds

this will get it for now (2010-05-25 07:56:23.000)

 SELECT dateadd(s,1274756183,'19700101 05:00:00:000')

If you want to go reverse, take a look at this http://wiki.lessthandot.com/index.php/Epoch_Date

Answer 5

Test this:

Sql server:

SELECT dateadd(S, timestamp, '1970-01-01 00:00:00') 
     FROM 
your_table

MySql server:

SELECT
  from_unixtime(timestamp) 
FROM 
  your_table

http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

Answer 6

This will do it:

declare @UNIX_TIME int
select @UNIX_TIME = 1111111111
-- Using dateadd to add seconds to 1970-01-01
select [Datetime from UNIX Time] = dateadd(!precision!,@UNIX_TIME,'1970-01-01')

Instead of !precision! use: ss,ms or mcs according to the precision of the timestamp. Bigint is capable to hold microsecond precision.

Answer 7

Adding n seconds to 1970-01-01 will give you a UTC date because n – the Unix timestamp – is the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.

In SQL Server 2016, you can convert one time zone to another using AT TIME ZONE. You need to specify the name of the time zone in Windows standard format:

SELECT *
FROM (VALUES
    (1514808000),
    (1527854400)
) AS Tests(UnixTimestamp)
CROSS APPLY (SELECT DATEADD(SECOND, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC') AS CA1(UTCDate)
CROSS APPLY (SELECT UTCDate AT TIME ZONE 'Pacific Standard Time') AS CA2(LocalDate)

UnixTimestamp	UTCDate	LocalDate
1514808000	2018-01-01 12:00:00 +00:00	2018-01-01 04:00:00 -08:00
1527854400	2018-06-01 12:00:00 +00:00	2018-06-01 05:00:00 -07:00

Or simply:

SELECT *, DATEADD(SECOND, UnixTimestamp, '1970-01-01')
              AT TIME ZONE 'UTC'
              AT TIME ZONE 'Pacific Standard Time' AS LocalDate
FROM (VALUES
    (1514808000),
    (1527854400)
) AS Tests(UnixTimestamp)

UnixTimestamp	LocalDate
1514808000	2018-01-01 04:00:00 -08:00
1527854400	2018-06-01 05:00:00 -07:00

Notes:

• The conversion takes daylight savings time into account. Pacific time was UTC-08:00 on January 2018 and UTC-07:00 on Jun 2018.
• You can chop off the timezone information by casting DATETIMEOFFSET to DATETIME.
• For full list of windows time zone "names", probe the sys.time_zone_info view or use tzutil.

Answer 8

If the time is in milliseconds and one need to preserve them:

DECLARE @value VARCHAR(32) = '1561487667713';

SELECT DATEADD(MILLISECOND, CAST(RIGHT(@value, 3) AS INT) - DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(@value, 10) AS INT), '1970-01-01T00:00:00'))

Answer 9

I had to face this problem, too. Unfortunately, none of the answers (here and in dozens of other pages) has been satisfactory to me, as I still cannot reach dates beyond the year 2038 due to 32 bit integer casts somewhere.

A solution that did work for me in the end was to use float variables, so I could have at least a max date of 2262-04-11T23:47:16.854775849. Still, this doesn't cover the entire datetime domain, but it is sufficient for my needs and may help others encountering the same problem.

-- date variables
declare @ts bigint; -- 64 bit time stamp, 100ns precision
declare @d datetime2(7) = GETUTCDATE(); -- 'now'
-- select @d = '2262-04-11T23:47:16.854775849'; -- this would be the max date

-- constants:
declare @epoch datetime2(7) = cast('1970-01-01T00:00:00' as datetime2(7));
declare @epochdiff int = 25567; -- = days between 1900-01-01 and 1970-01-01
declare @ticksofday bigint = 864000000000; -- = (24*60*60*1000*1000*10)

-- helper variables:
declare @datepart float;
declare @timepart float;
declare @restored datetime2(7);

-- algorithm:
select @ts = DATEDIFF_BIG(NANOSECOND, @epoch, @d) / 100; -- 'now' in ticks according to unix epoch
select @timepart = (@ts % @ticksofday) / @ticksofday; -- extract time part and scale it to fractional part (i. e. 1 hour is 1/24th of a day)
select @datepart = (@ts - @timepart) / @ticksofday; -- extract date part and scale it to fractional part
select @restored = cast(@epochdiff + @datepart + @timepart as datetime); -- rebuild parts to a datetime value

-- query original datetime, intermediate timestamp and restored datetime for comparison
select
  @d original,
  @ts unix64,
  @restored restored
;

-- example result for max date:
-- +-----------------------------+-------------------+-----------------------------+
-- | original                    | unix64            | restored                    |
-- +-----------------------------+-------------------+-----------------------------+
-- | 2262-04-11 23:47:16.8547758 | 92233720368547758 | 2262-04-11 23:47:16.8533333 |
-- +-----------------------------+-------------------+-----------------------------+

There are some points to consider:

• 100ns precision is the requirement in my case, however this seems to be the standard resolution for 64 bit unix timestamps. If you use any other resolution, you have to adjust @ticksofday and the first line of the algorithm accordingly.
• I'm using other systems that have their problems with time zones etc. and I found the best solution for me would be always using UTC. For your needs, this may differ.
• 1900-01-01 is the origin date for datetime2, just as is the epoch 1970-01-01 for unix timestamps.
• floats helped me to solve the year-2038-problem and integer overflows and such, but keep in mind that floating point numbers are not very performant and may slow down processing of a big amount of timestamps. Also, floats may lead to loss of precision due to roundoff errors, as you can see in the comparison of the example results for the max date above (here, the error is about 1.4425ms).
• In the last line of the algorithm there is a cast to datetime. Unfortunately, there is no explicit cast from numeric values to datetime2 allowed, but it is allowed to cast numerics to datetime explicitly and this, in turn, is cast implicitly to datetime2. This may be correct, for now, but may change in future versions of SQL Server: Either there will be a dateadd_big() function or the explicit cast to datetime2 will be allowed or the explicit cast to datetime will be disallowed, so this may either break or there may come an easier way some day.

Answer 10

@DanielLittle has the easiest and most elegant answer to the specific question. However, if you are interested in converting to a specific timezone AND taking into account DST (Daylight Savings Time), the following works well:

CAST(DATEADD(S, [UnixTimestamp], '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS Datetime)

Note: This solution only works on SQL Server 2016 and above (and Azure).

To create a function:

CREATE FUNCTION dbo.ConvertUnixTime (@input INT)
RETURNS Datetime
AS BEGIN
    DECLARE @Unix Datetime

    SET @Unix = CAST(DATEADD(S, @Input, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS Datetime)

    RETURN @Unix
END

You can call the function like so:

SELECT   dbo.ConvertUnixTime([UnixTimestamp])
FROM     YourTable

Answer 11

This is building off the work Daniel Little did for this question, but taking into account daylight savings time (works for dates 01-01 1902 and greater due to int limit on dateadd function):

We first need to create a table that will store the date ranges for daylight savings time (source: History of time in the United States):

CREATE TABLE [dbo].[CFG_DAY_LIGHT_SAVINGS_TIME](
  [BEGIN_DATE] [datetime] NULL,
  [END_DATE] [datetime] NULL,
  [YEAR_DATE] [smallint] NULL
) ON [PRIMARY]

GO

INSERT INTO CFG_DAY_LIGHT_SAVINGS_TIME VALUES
('2001-04-01 02:00:00.000',   '2001-10-27 01:59:59.997',    2001),
('2002-04-07 02:00:00.000',   '2002-10-26 01:59:59.997',    2002),
('2003-04-06 02:00:00.000',   '2003-10-25 01:59:59.997',    2003),
('2004-04-04 02:00:00.000',   '2004-10-30 01:59:59.997',    2004),
('2005-04-03 02:00:00.000',   '2005-10-29 01:59:59.997',    2005),
('2006-04-02 02:00:00.000',   '2006-10-28 01:59:59.997',    2006),
('2007-03-11 02:00:00.000',   '2007-11-03 01:59:59.997',    2007),
('2008-03-09 02:00:00.000',   '2008-11-01 01:59:59.997',    2008),
('2009-03-08 02:00:00.000',   '2009-10-31 01:59:59.997',    2009),
('2010-03-14 02:00:00.000',   '2010-11-06 01:59:59.997',    2010),
('2011-03-13 02:00:00.000',   '2011-11-05 01:59:59.997',    2011),
('2012-03-11 02:00:00.000',   '2012-11-03 01:59:59.997',    2012),
('2013-03-10 02:00:00.000',   '2013-11-02 01:59:59.997',    2013),
('2014-03-09 02:00:00.000',   '2014-11-01 01:59:59.997',    2014),
('2015-03-08 02:00:00.000',   '2015-10-31 01:59:59.997',    2015),
('2016-03-13 02:00:00.000',   '2016-11-05 01:59:59.997',    2016),
('2017-03-12 02:00:00.000',   '2017-11-04 01:59:59.997',    2017),
('2018-03-11 02:00:00.000',   '2018-11-03 01:59:59.997',    2018),
('2019-03-10 02:00:00.000',   '2019-11-02 01:59:59.997',    2019),
('2020-03-08 02:00:00.000',   '2020-10-31 01:59:59.997',    2020),
('2021-03-14 02:00:00.000',   '2021-11-06 01:59:59.997',    2021),
('2022-03-13 02:00:00.000',   '2022-11-05 01:59:59.997',    2022),
('2023-03-12 02:00:00.000',   '2023-11-04 01:59:59.997',    2023),
('2024-03-10 02:00:00.000',   '2024-11-02 01:59:59.997',    2024),
('2025-03-09 02:00:00.000',   '2025-11-01 01:59:59.997',    2025),
('1967-04-30 02:00:00.000',   '1967-10-29 01:59:59.997',    1967),
('1968-04-28 02:00:00.000',   '1968-10-27 01:59:59.997',    1968),
('1969-04-27 02:00:00.000',   '1969-10-26 01:59:59.997',    1969),
('1970-04-26 02:00:00.000',   '1970-10-25 01:59:59.997',    1970),
('1971-04-25 02:00:00.000',   '1971-10-31 01:59:59.997',    1971),
('1972-04-30 02:00:00.000',   '1972-10-29 01:59:59.997',    1972),
('1973-04-29 02:00:00.000',   '1973-10-28 01:59:59.997',    1973),
('1974-01-06 02:00:00.000',   '1974-10-27 01:59:59.997',    1974),
('1975-02-23 02:00:00.000',   '1975-10-26 01:59:59.997',    1975),
('1976-04-25 02:00:00.000',   '1976-10-31 01:59:59.997',    1976),
('1977-04-24 02:00:00.000',   '1977-10-31 01:59:59.997',    1977),
('1978-04-30 02:00:00.000',   '1978-10-29 01:59:59.997',    1978),
('1979-04-29 02:00:00.000',   '1979-10-28 01:59:59.997',    1979),
('1980-04-27 02:00:00.000',   '1980-10-26 01:59:59.997',    1980),
('1981-04-26 02:00:00.000',   '1981-10-25 01:59:59.997',    1981),
('1982-04-25 02:00:00.000',   '1982-10-25 01:59:59.997',    1982),
('1983-04-24 02:00:00.000',   '1983-10-30 01:59:59.997',    1983),
('1984-04-29 02:00:00.000',   '1984-10-28 01:59:59.997',    1984),
('1985-04-28 02:00:00.000',   '1985-10-27 01:59:59.997',    1985),
('1986-04-27 02:00:00.000',   '1986-10-26 01:59:59.997',    1986),
('1987-04-05 02:00:00.000',   '1987-10-25 01:59:59.997',    1987),
('1988-04-03 02:00:00.000',   '1988-10-30 01:59:59.997',    1988),
('1989-04-02 02:00:00.000',   '1989-10-29 01:59:59.997',    1989),
('1990-04-01 02:00:00.000',   '1990-10-28 01:59:59.997',    1990),
('1991-04-07 02:00:00.000',   '1991-10-27 01:59:59.997',    1991),
('1992-04-05 02:00:00.000',   '1992-10-25 01:59:59.997',    1992),
('1993-04-04 02:00:00.000',   '1993-10-31 01:59:59.997',    1993),
('1994-04-03 02:00:00.000',   '1994-10-30 01:59:59.997',    1994),
('1995-04-02 02:00:00.000',   '1995-10-29 01:59:59.997',    1995),
('1996-04-07 02:00:00.000',   '1996-10-27 01:59:59.997',    1996),
('1997-04-06 02:00:00.000',   '1997-10-26 01:59:59.997',    1997),
('1998-04-05 02:00:00.000',   '1998-10-25 01:59:59.997',    1998),
('1999-04-04 02:00:00.000',   '1999-10-31 01:59:59.997',    1999),
('2000-04-02 02:00:00.000',   '2000-10-29 01:59:59.997',    2000)
GO

Now we create a function for each American timezone. This is assuming the unix time is in milliseconds. If it is in seconds, remove the /1000 from the code:

Pacific

create function [dbo].[UnixTimeToPacific] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @pacificdatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @pacificdatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -7 else -8 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @pacificdatetime is null 
       select @pacificdatetime= dateadd(hour, -7, @interimdatetime)
return @pacificdatetime    
end

Eastern

create function [dbo].[UnixTimeToEastern] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @easterndatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @easterndatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -4 else -5 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @easterndatetime is null 
       select @easterndatetime= dateadd(hour, -4, @interimdatetime)
return @easterndatetime    
end

Central

create function [dbo].[UnixTimeToCentral] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @centraldatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @centraldatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -5 else -6 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @centraldatetime is null 
       select @centraldatetime= dateadd(hour, -5, @interimdatetime)
return @centraldatetime    
end

Mountain

create function [dbo].[UnixTimeToMountain] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @mountaindatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @mountaindatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -6 else -7 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @mountaindatetime is null 
       select @mountaindatetime= dateadd(hour, -6, @interimdatetime)
return @mountaindatetime    
end

Hawaii

create function [dbo].[UnixTimeToHawaii] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @hawaiidatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @hawaiidatetime =  dateadd(hour,-10,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)

return @hawaiidatetime    
end

Arizona

create function [dbo].[UnixTimeToArizona] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @arizonadatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @arizonadatetime =  dateadd(hour,-7,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)

return @arizonadatetime    
end

Alaska

create function [dbo].[UnixTimeToAlaska] 
 (@unixtime bigint)
   returns datetime
   as
   begin
     declare @alaskadatetime datetime
     declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01')
     select  @alaskadatetime =  dateadd(hour,case when @interimdatetime between begin_date and end_date then -8 else -9 end  ,@interimdatetime)
     from cfg_day_light_savings_time  where  year_date = datepart(year,@interimdatetime)
     if @alaskadatetime is null 
       select @alaskadatetime= dateadd(hour, -8, @interimdatetime)
return @alaskadatetime    
end

Answer 12

//BIGINT UNIX TIMESTAMP CONVERSION upto Millisecond Accuracy
CREATE FUNCTION [dbo].[ConvertUnixTimestamp] (@Datetime [BIGINT]) RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(MILLISECOND, cast(@Datetime as bigint) % 1000, 
    DATEADD(SECOND, (cast(@Datetime as bigint) / 1000)%60, 
    DATEADD(MINUTE, ((cast(@Datetime as bigint) / 1000)/60)%60, 
    DATEADD(HOUR, ((cast(@Datetime as bigint) / 1000)/60)/60, '19700101'))))
END

Answer 13

For GMT, here is the easiest way:

Select dateadd(s, @UnixTime+DATEDIFF (S, GETUTCDATE(), GETDATE()), '1970-01-01')

Answer 14

CREATE FUNCTION dbo.ConvertUnixToDateTime(@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN 
    RETURN (SELECT DATEADD(second,@Datetime, CAST('1970-01-01' AS datetime)))
END;
GO

Answer 15

Better? This function converts unixtime in milliseconds to datetime. It's lost milliseconds, but still very useful for filtering.

CREATE FUNCTION [dbo].[UnixTimestampToGMTDatetime] 
(@UnixTimestamp bigint)
RETURNS datetime
AS
BEGIN
       DECLARE @GMTDatetime datetime
       select @GMTDatetime = 
       CASE
       WHEN dateadd(ss, @UnixTimestamp/1000, '1970-01-01') 
       BETWEEN 
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 4) % 7)) + ' 01:00:00', 20)
       AND
           Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 1) % 7)) + ' 02:00:00', 20)
       THEN Dateadd(hh, 1, dateadd(ss, @UnixTimestamp/1000, '1970-01-01'))
       ELSE Dateadd(hh, 0, dateadd(ss, @UnixTimestamp/1000, '1970-01-01'))
       END
RETURN @GMTDatetime    
END

Answer 16

Solution can be the following:

DECLARE @UnixTimeStamp bigint = 1564646400000 /*2019-08-01 11:00 AM*/

DECLARE @LocalTimeOffset bigint = DATEDIFF(MILLISECOND, GETDATE(), GETUTCDATE());
DECLARE @AdjustedTimeStamp bigint = @UnixTimeStamp - @LocalTimeOffset;
SELECT [DateTime] = DATEADD(SECOND, @AdjustedTimeStamp % 1000, DATEADD(SECOND, @AdjustedTimeStamp / 1000, '19700101'));

Answer 17

Here's the function I use, which works for dates larger than 2038 by doing a two-step dateadd(). It returns UTC, but see e.g. Kenny's answer for timezone and DST handling.

IF OBJECT_ID('dbo.fn_ConvertUnixToDateTime') IS NULL
  EXEC ('CREATE function dbo.fn_ConvertUnixToDateTime() returns int AS begin RETURN 0 end;')
GO
go 
alter function dbo.fn_ConvertUnixToDateTime (@unixTimestamp BIGINT)
RETURNS DATETIME
AS
/* 
    Converts unix timestamp to utc datetime. 

    To work with larger timestamps it does a two-part add, since dateadd()
    function only allows you to add int values, not bigint. 
*/ 
BEGIN
    RETURN (SELECT DATEADD( second
                        ,   @unixTimestamp % 3600
                        ,   dateadd(    hour
                                    ,   @unixTimestamp / 3600
                                    ,   CAST('1970-01-01 00:00:00' AS datetime)
                                    )
                        )
            )

END;

Answer 18

On SQL server you can try this:

SELECT dateadd(S, CAST(CONVERT(INT, CONVERT(VARBINARY, SUBSTRing('Timestramp', 1, 8), 2)) AS NUMERIC(20)), '1970-01-01')

Example:

SELECT dateadd(S, CAST(CONVERT(INT, CONVERT(VARBINARY, SUBSTRING('6369841c05df306d5dc81914', 1, 8), 2)) AS NUMERIC(20)), '1970-01-01')

Answer 19

@salman , @daniel-little

I think that Daniel's answer is wrong.

His logic calculates using only seconds of leap year. So converted values are not correct.

I think that we need the function as follows:

CREATE OR ALTER FUNCTION dbo.convert_unixtime_to_utc_datetime
(
  @source_unix_time AS BIGINT
)
RETURNS DATETIME2
  BEGIN
    
    DECLARE @remaining_unix_time AS BIGINT = @source_unix_time
    DECLARE @max_int_value AS BIGINT = 2147483647
    DECLARE @unixtime_base_year AS INT = 1970
    DECLARE @target_year AS INT = @unixtime_base_year
    DECLARE @year_offset AS INT = 0
    DECLARE @subtract_value AS BIGINT = 0
    DECLARE @calc_base_datetime AS DATETIME2
    DECLARE @seconds_per_day AS BIGINT = (60 /* seconds */ * 60 /* minutes */ * 24 /* hours */)
    
    WHILE (1 = 1)
      BEGIN
        IF @remaining_unix_time <= @max_int_value
          BREAK
        
        IF ((@target_year % 400 = 0) OR ((@target_year % 4 = 0) AND (@target_year % 100 != 0)))
          SET @subtract_value = (@seconds_per_day * 366 /* days */)
        ELSE
          SET @subtract_value = (@seconds_per_day * 365 /* days */)

        SET @remaining_unix_time -= @subtract_value
        SET @target_year += 1
        SET @year_offset += 1
        
      END
  
    SET @calc_base_datetime = DATETIME2FROMPARTS(@unixtime_base_year + @year_offset, 1, 1, 0, 0, 0, 0, 0)

    RETURN DATEADD(SECOND, @remaining_unix_time, @calc_base_datetime)
  END
;

I verified edge cases using components as follows:

• DDL

CREATE TABLE test_convert_unixtime_table (
  id BIGINT NOT NULL
  , description nvarchar(max) NOT NULL
  , source_utc_datetime datetime2 NOT NULL
  , source_unixtime BIGINT NOT NULL
  , CONSTRAINT pkc_test_convert_unixtime_table PRIMARY KEY (id)
) ;

ALTER TABLE test_convert_unixtime_table ADD CONSTRAINT idx_test_convert_unixtime_table_1
  UNIQUE (source_unixtime) ;

• DML

INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (2, 'threshold of 2038 year problem', '2038/01/19 03:14:07', 2147483647);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (3, 'threshold of 2038 year problem + 1 second', '2038/01/19 03:14:08', 2147483648);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (4, 'leap year - 1 year before 2038 
the first day of the year', '2035/01/01 00:00:00', 2051222400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (5, 'leap year - 1 year before 2038 
the end of Feburary', '2035/02/28 23:59:59', 2056319999);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (6, 'leap year - 1 year before 2038 
the first day of March', '2035/03/01 00:00:00', 2056320000);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (7, 'leap year - 1 year before 2038 
new year's eve', '2035/12/31 23:59:59', 2082758399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (8, 'leap year before 2038 
the first day of the year', '2036/01/01 00:00:00', 2082758400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (9, 'leap year before 2038 
the end of Feburary', '2036/02/29 23:59:59', 2087942399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (10, 'leap year before 2038 
the first day of March', '2036/03/01 00:00:00', 2087942400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (11, 'leap year before 2038 
new year's eve', '2036/12/31 23:59:59', 2114380799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (12, 'leap year + 1 year before 2038 
the first day of the year', '2037/01/01 00:00:00', 2114380800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (13, 'leap year + 1 year before 2038 
the end of Feburary', '2037/02/28 23:59:59', 2119478399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (14, 'leap year + 1 year before 2038 
the first day of March', '2037/03/01 00:00:00', 2119478400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (15, 'leap year + 1 year before 2038 
new year's eve', '2037/12/31 23:59:59', 2145916799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (16, 'leap year - 1 year after 2038 
the first day of the year', '2039/01/01 00:00:00', 2177452800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (17, 'leap year - 1 year after 2038 
the end of Feburary', '2039/02/28 23:59:59', 2182550399);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (18, 'leap year - 1 year after 2038 
the first day of March', '2039/03/01 00:00:00', 2182550400);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (19, 'leap year - 1 year after 2038 
new year's eve', '2039/12/31 23:59:59', 2208988799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (20, 'leap year after 2038 
the first day of the year', '2040/01/01 00:00:00', 2208988800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (21, 'leap year after 2038 
the end of Feburary', '2040/02/29 23:59:59', 2214172799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (22, 'leap year after 2038 
the first day of March', '2040/03/01 00:00:00', 2214172800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (23, 'leap year after 2038 
new year's eve', '2040/12/31 23:59:59', 2240611199);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (24, 'leap year + 1 year after 2038 
the first day of the year', '2041/01/01 00:00:00', 2240611200);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (25, 'leap year + 1 year after 2038 
the end of Feburary', '2041/02/28 23:59:59', 2245708799);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (26, 'leap year + 1 year after 2038 
the first day of March', '2041/03/01 00:00:00', 2245708800);
INSERT INTO test_convert_unixtime_table (id, description, source_utc_datetime, source_unixtime) VALUES (27, 'leap year + 1 year after 2038 
new year's eve', '2041/12/31 23:59:59', 2272147199);

• Query

SELECT 
  *
  , dbo.convert_unixtime_to_utc_datetime(source_unixtime) AS [by_myfunc]
  , dbo.func_by_daniel_little(source_unixtime) AS [by_daniel_little]
FROM
  test_convert_unixtime_table
ORDER BY
  id;

• ResultSet

|id |description                                             |source_utc_datetime    |source_unixtime|by_myfunc              |by_daniel_little       |
|---|--------------------------------------------------------|-----------------------|---------------|-----------------------|-----------------------|
|1  |threshold of 2038 year problem - 1 second               |2038/01/19 03:14:06.000|2,147,483,646  |2038/01/19 03:14:06.000|2037/11/30 03:14:06.000|
|2  |threshold of 2038 year problem                          |2038/01/19 03:14:07.000|2,147,483,647  |2038/01/19 03:14:07.000|2037/11/30 03:14:07.000|
|3  |threshold of 2038 year problem + 1 second               |2038/01/19 03:14:08.000|2,147,483,648  |2038/01/19 03:14:08.000|2037/11/30 03:14:08.000|
|4  |leap year - 1 year before 2038 the first day of the year|2035/01/01 00:00:00.000|2,051,222,400  |2035/01/01 00:00:00.000|2034/11/14 00:00:00.000|
|5  |leap year - 1 year before 2038 the end of Feburary      |2035/02/28 23:59:59.000|2,056,319,999  |2035/02/28 23:59:59.000|2035/01/10 23:59:59.000|
|6  |leap year - 1 year before 2038 the first day of March   |2035/03/01 00:00:00.000|2,056,320,000  |2035/03/01 00:00:00.000|2035/01/11 00:00:00.000|
|7  |leap year - 1 year before 2038 new year's eve           |2035/12/31 23:59:59.000|2,082,758,399  |2035/12/31 23:59:59.000|2035/11/12 23:59:59.000|
|8  |leap year before 2038 the first day of the year         |2036/01/01 00:00:00.000|2,082,758,400  |2036/01/01 00:00:00.000|2035/11/13 00:00:00.000|
|9  |leap year before 2038 the end of Feburary               |2036/02/29 23:59:59.000|2,087,942,399  |2036/02/29 23:59:59.000|2036/01/10 23:59:59.000|
|10 |leap year before 2038 the first day of March            |2036/03/01 00:00:00.000|2,087,942,400  |2036/03/01 00:00:00.000|2036/01/11 00:00:00.000|
|11 |leap year before 2038 new year's eve                    |2036/12/31 23:59:59.000|2,114,380,799  |2036/12/31 23:59:59.000|2036/11/11 23:59:59.000|
|12 |leap year + 1 year before 2038 the first day of the year|2037/01/01 00:00:00.000|2,114,380,800  |2037/01/01 00:00:00.000|2036/11/12 00:00:00.000|
|13 |leap year + 1 year before 2038 the end of Feburary      |2037/02/28 23:59:59.000|2,119,478,399  |2037/02/28 23:59:59.000|2037/01/09 23:59:59.000|
|14 |leap year + 1 year before 2038 the first day of March   |2037/03/01 00:00:00.000|2,119,478,400  |2037/03/01 00:00:00.000|2037/01/10 00:00:00.000|
|15 |leap year + 1 year before 2038 new year's eve           |2037/12/31 23:59:59.000|2,145,916,799  |2037/12/31 23:59:59.000|2037/11/11 23:59:59.000|
|16 |leap year - 1 year after 2038 the first day of the year |2039/01/01 00:00:00.000|2,177,452,800  |2039/01/01 00:00:00.000|2038/11/11 00:00:00.000|
|17 |leap year - 1 year after 2038 the end of Feburary       |2039/02/28 23:59:59.000|2,182,550,399  |2039/02/28 23:59:59.000|2039/01/07 23:59:59.000|
|18 |leap year - 1 year after 2038 the first day of March    |2039/03/01 00:00:00.000|2,182,550,400  |2039/03/01 00:00:00.000|2039/01/08 00:00:00.000|
|19 |leap year - 1 year after 2038 new year's eve            |2039/12/31 23:59:59.000|2,208,988,799  |2039/12/31 23:59:59.000|2039/11/09 23:59:59.000|
|20 |leap year after 2038 the first day of the year          |2040/01/01 00:00:00.000|2,208,988,800  |2040/01/01 00:00:00.000|2039/11/10 00:00:00.000|
|21 |leap year after 2038 the end of Feburary                |2040/02/29 23:59:59.000|2,214,172,799  |2040/02/29 23:59:59.000|2040/01/07 23:59:59.000|
|22 |leap year after 2038 the first day of March             |2040/03/01 00:00:00.000|2,214,172,800  |2040/03/01 00:00:00.000|2040/01/08 00:00:00.000|
|23 |leap year after 2038 new year's eve                     |2040/12/31 23:59:59.000|2,240,611,199  |2040/12/31 23:59:59.000|2040/11/08 23:59:59.000|
|24 |leap year + 1 year after 2038 the first day of the year |2041/01/01 00:00:00.000|2,240,611,200  |2041/01/01 00:00:00.000|2040/11/09 00:00:00.000|
|25 |leap year + 1 year after 2038 the end of Feburary       |2041/02/28 23:59:59.000|2,245,708,799  |2041/02/28 23:59:59.000|2041/01/06 23:59:59.000|
|26 |leap year + 1 year after 2038 the first day of March    |2041/03/01 00:00:00.000|2,245,708,800  |2041/03/01 00:00:00.000|2041/01/07 00:00:00.000|
|27 |leap year + 1 year after 2038 new year's eve            |2041/12/31 23:59:59.000|2,272,147,199  |2041/12/31 23:59:59.000|2041/11/08 23:59:59.000|

Returned values by my function all match source datetime, however, Retruned values by Daniel's logic do not all match source datetime.

Answer 20

CAST(DATEADD(S, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Central Europe Standard Time' AS Datetime)

In combination with sys.time_zone_info, you can check your local timezone.