90abyss 90abyss - 4 years ago 193
SQL Question

Getting a "Adding a value to a 'datetime2' column caused an overflow" only when operating on large dataset

I'm basically calculating the difference between two dates in the format days:hours:mins:sec

CONVERT(varchar, datediff (s, [CreatedDate], [ClosedDate]) / (60 * 60 * 24)) + ':' + CONVERT(varchar, dateadd(s, datediff (s, [CreatedDate], [ClosedDate]), CONVERT(datetime2, '0001-01-01')), 108) AS Length


Both [CreatedDate], [ClosedDate] are in DATETIME2

If I do:

SELECT top 10000
then it works as expected

But when I do a
SELECT *
then I get this error:


Adding a value to a 'datetime2' column caused an overflow.


Is my method not optimal enough? What do I need to change?

Thanks.

Also - The minimum date for both of these is 2015-07-01 so I think this won't apply here: http://www.devx.com/tips/Tip/13898

Answer Source
Declare @YourTable table ([CreatedDate] datetime2, [ClosedDate] datetime2)
Insert Into @YourTable values
('2015-07-01 12:30:00','2015-07-01 12:30:02'),  -- 2 sec
('2015-07-01 12:30:00','2015-07-01 12:35:02'),  -- 5 min 2 sec
('2015-07-01 12:30:00','2015-07-01 17:35:02'),  -- 5 hrs 5 min 2 sec
('2015-07-01 12:30:00','2015-07-02 12:30:00'),  -- exactly 1 day
('2015-07-01 12:30:00','2015-07-02 12:00:00'),  -- 30 min short of 1 day
('2015-07-01 12:30:00','2017-02-06 16:00:00'),   -- more than 1 year
('2015-07-01 12:30:00',null)                    -- Default to current datetime

Select *
      ,Elapsed = concat(DateDiff(SS,[CreatedDate],IsNull([ClosedDate],GetDate()))/86400,Format(DateAdd(S,DateDiff(SECOND,[CreatedDate],IsNull([ClosedDate],GetDate())),0),':HH:mm:ss'))
 From  @YourTable

Returns

CreatedDate                 ClosedDate                      Elapsed
2015-07-01 12:30:00.0000000 2015-07-01 12:30:02.0000000     0:00:00:02
2015-07-01 12:30:00.0000000 2015-07-01 12:35:02.0000000     0:00:05:02
2015-07-01 12:30:00.0000000 2015-07-01 17:35:02.0000000     0:05:05:02
2015-07-01 12:30:00.0000000 2015-07-02 12:30:00.0000000     1:00:00:00
2015-07-01 12:30:00.0000000 2015-07-02 12:00:00.0000000     0:23:30:00
2015-07-01 12:30:00.0000000 2017-02-06 16:00:00.0000000   586:03:30:00
2015-07-01 12:30:00.0000000 NULL                          586:04:15:59
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download