CODEWITHSUNDEEP
sqlsql-servert-sql
shalabh
shalabh

Reputation: 97

Case expression gives "conversion of a date data type to a datetime data type resulted in an out-of-range value."

I have following table and table data. After which when I execute the select statement using cases, then I am getting error

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

But when I comment my second case then select works fine. Please help me to understand this issue

CREATE TABLE TEST (
ID varchar(20),
T_Date date,
FLAG varchar(20)
);

insert into TEST values('1234', '0001-01-01', NULL);

select t.T_Date, t.FLAG,
CASE
    WHEN t.FLAG IS NULL AND t.T_Date IS NOT NULL AND t.T_Date != '2001-01-01' THEN t.T_Date
    WHEN t.FLAG IS NULL AND (t.T_Date IS NULL OR (t.T_Date IS NOT NULL AND t.T_Date = '2001-01-01')) THEN CONVERT(datetime, '20201216', 112) + 10
END as t_date
FROM TEST t

Upvotes: 0

Views: 639

Answers (2)

Dale K
Dale K

Reputation: 27225

Because every branch of a case expression must return the same datatype. So with your convert to datetime in the second branch you forced SQL Server to try and convert your first branch to datetime also due to datatype precedence.

And when, in the first branch, you try and convert your date value, "0001-01-01", to datetime it fails, because the lowest value datetime can handle is "January 1, 1753".

You also cannot add 10 to a date, nor should you, even though datetime allows it. What are you adding? Days? Hours? Minutes? Use the dateadd function to correctly add to a date.

SELECT t.T_Date, t.FLAG,
    CASE
    WHEN t.FLAG IS NULL AND t.T_Date IS NOT NULL AND t.T_Date != '2001-01-01' THEN t.T_Date
    WHEN t.FLAG IS NULL AND (t.T_Date IS NULL OR (t.T_Date IS NOT NULL AND t.T_Date = '2001-01-01')) THEN DATEADD(DAY, 10, CONVERT(DATE, '20201216', 112))
    END AS t_date
FROM #TEST t

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

A CASE expression has to produce a value of one fixed type, no matter which clause ends up matching.

datetime has a higher precedence than date, and is therefore the type of your CASE expression.

A datetime is incapable of storing a date before 1753. Your date value is in the year 1.

As suggested (for at least a decade):

Use the time, date, datetime2 and datetimeoffset data types for new work.

select t.T_Date, t.FLAG,
CASE
    WHEN t.FLAG IS NULL AND t.T_Date IS NOT NULL AND t.T_Date != '2001-01-01'
       THEN t.T_Date
    WHEN t.FLAG IS NULL AND (t.T_Date IS NULL OR (t.T_Date IS NOT NULL AND t.T_Date = '2001-01-01'))
       THEN CONVERT(date, '20201226', 112)
END as t_date
FROM TEST t

Upvotes: 1

Related Questions