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:
@ticksofday and the first line of the algorithm accordingly.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).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.