90abyss 90abyss - 4 years ago 96
SQL Question

How to resolve: DateAdd results into an out-of-range value for DATETIME?

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


The above works when CreatedDate and ClosedDate are in DATETIME2.

But gives me this error when they are in DATETIME:


The conversion of a varchar data type to a datetime data type resulted
in an out-of-range value.


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


How can I fix this?

Answer Source

One method is to get the seconds between the dates so the interval" is actually a datetime from time zero:

dateadd(second, datediff(second, [CreatedDate], [ClosedDate]), 0)

Then convert this to a format of "dd hh:mi:ss":

right(convert(varchar(255),
              dateadd(second, datediff(second, [CreatedDate], [ClosedDate]), 0)
              120), 11)

In SQL Server 2012+, you can use format().

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download