coalesce error

Question

Hi ,

It got this error The conversion of a date data type to a datetime data type resulted in an out-of-range value from the statement 1 below.
1: coalesce(e1.[ResDate],e2.[ReDate],case when year(c.mydct)!=1970 then dateadd(minute,-360,c.mydct) else '' '' end) [schedule date]

Tried separately
a- isnull(e1.[ResDate],case when year(c.mydct)!=1970 then dateadd(minute,-360,c.mydct) else '' '' end) - worked fine no error
b - coalesce(e1.[ResDate],case when year(c.mydct)!=1970 then dateadd(minute,-360,c.mydct) else '' '' end) same error from #1.

Why did I get this error and what should I do ?

Thanks

Answer 1

coalesce(e1.[ResDate],e2.[ReDate],case when year(c.mydct) <>1970 then dateadd(minute,-360,c.mydct) else null end) [schedule date]

--or
coalesce(e1.[ResDate],e2.[ReDate],case when year(c.mydct)!=1970 then dateadd(minute,-360,c.mydct) else '1900-01-01' end) [schedule date]

Answer 2

We don't know your data, nor do we know your tables and columns, but it appears that you have a mix of the date and datetime data types there. The range for date is from 0001-01-01 to 9999-12-31. datetime has the same upper end, but the low end is 1753-01-01, so it is perfectly conceivable to get that error. You can avoid this risk by using datetime2(0) instead, as datetime2 has the same range as date.

The expression of the coalesce expression is the data type with highest precedence, and datetime has higher precedence. isnull is different, is with isnull the data type is determined by the first argument.

The " " in the ELSE is a little funny. There is no such thing s a blank date, but you will get back 1900-01-01 00:00:00.000.

Answer 3

Hi HongHo,

SQL server time limit：
datetime 1753-01-01 to 9999-12-31 ,00:00:00 to 23:59:59.997 3.33 milliseconds，
datetime2 0001-01-01 to 9999-12-31, 00:00:00 to 23:59:59.9999999 100 nanoseconds.
Not only will an error returned if the date is less than 1753-01-01,
but an error will also be returned if the date is incorrect, such as 1997-2-29.
You can try to change the datetime type to datetime2 type.

In addition, there is another problem with your statement,please refer to:

coalesce(e1.[ResDate],e2.[ReDate],case when year(c.mydct)!=1970 then dateadd(minute,-360,c.mydct) else '' end) [schedule date]

I have answered similar questions on MSDN, you can refer to：
Msg 242, Level 16, State 3, Line 2 The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

Best Regards
Echo