How to support bigint in datetime

Question

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

How can I get the bigint value in its equivalent date and vice versa if the maximum date can be int in SQL?

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

Any ideas would be appreciated.

Thanks!

Answer 1

This will give you milliseconds. 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> 

Answer 2

You can use datetimes as a float, where they will indicate the number of days from 1900-01-01 00:00:00. Parts are less than days, then just fractions. Allocation of these numbers and scaling in seconds per day should be performed.

DECLARE @ d1 DATETIME

DECLARE @ d2 DATETIME

SELECT @ d1 = '01 / 01/1970 '

SELECT @ d2 = '12 / 31/2050 '

SELECT (CAST (@ d2 AS FLOAT) - CAST (@ d1 AS FLOAT)) * 3600 * 24

But you can probably find more pleasant solutions;)

Answer 3

 CREATE FUNCTION dbo.SecondsSince1970(@date datetime) RETURNS bigint AS BEGIN RETURN CASE WHEN @date > '2038-01-19' THEN CAST(DATEDIFF(ss, '2038-01-19', @date) AS bigint) + DATEDIFF(ss, '1970-01-01', '2038-01-19') ELSE DATEDIFF(ss, '1970-01-01', @date) END END