How to support bigint in datetime

Question

The result of datediff(ss, '01/01/1970', '12/31/2050') is a bigint so datediff is overflowing.

How can I get from a bigint value to its equivalent date and back again if the max a date can be in SQL is int?

I need to be able to store number of seconds between 01/01/1970 and 12/31/2050 in SQL (which I do as a char) but convert that value to its calendar date for display in a web page.

Any ideas would be appreciated.

Thank you!

Answer 1

This will give you miliseconds. Easy to adapt to seconds...

declare @dfrom datetime = '1970-01-01 16:15:33.021'
declare @dto datetime = '2058-01-01 15:00:55.557'

declare @diff bigint = 
    cast(DATEDIFF(d, @dfrom, @dto) as bigint) * 24 * 3600 * 1000 
     + DATEDIFF(ms, cast(@dfrom as time), cast(@dto as time))    

declare @dreverse datetime = 
    dateadd(ms, @diff % (1000 * 3600 * 24), 
        dateadd(day, @diff / (1000 * 3600 * 24), @dfrom))   

select @dfrom as [From], @dto as [To], @diff as [Diff], @dreverse as [Reverse] for xml path('')

gives:

<From>1970-01-01T16:15:33.020</From>
<To>2058-01-01T15:00:55.557</To>
<Diff>2777064322537</Diff>
<Reverse>2058-01-01T15:00:55.557</Reverse>