CODEWITHSUNDEEP
sqlsql-servert-sql
90abyss
90abyss

Reputation: 7347

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?

Upvotes: 0

Views: 1454

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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().

Upvotes: 1

Related Questions