CASE expression yields error when mixing types

Question

Consider the following:

SELECT [DOB] = CASE WHEN [DOB] = '-' then 'invalid date' ELSE cast([DOB] as datetime) 
END
FROM myTable where myID = 73351

When DOB = '-', I get the error message:

Conversion failed when converting datetime from character string.

Which seems to indicate SQL Server tries to cast DOB as datetime despite the fact it should only print 'invalid date'.

Yet consider this which correctly returns 'invalid date' when DOB = '-'.

SELECT [DOB] = CASE WHEN [DOB] = '-' then 'invalid date' ELSE 'valid date'
END
FROM myTable where myID = 73351

How can I get the first statement to return 'invalid date' when DOB = '-'?

Mikael Eriksson · Accepted Answer · 2013-07-08T17:27:53.237

2

The case expression will result in only one data type. You can not have different data types of different rows. The data type in your query will be a datetime and invalid date is an invalid datetime value.

How can I get the first statement to return 'invalid date' when DOB = '-' ?

The only way is to not cast the value to datetime.

CASE WHEN [DOB] = '-' then 'invalid date' ELSE [DOB] END

edited Jul 08 '13 at 17:27

answered Jul 08 '13 at 17:22

Mikael Eriksson

136,425
22
210
281

casting to sql_variant works http://msdn.microsoft.com/en-us/library/ms173829(v=sql.90).aspx – Louis Ricci Jul 08 '13 at 17:38
@LastCoder I have never used them. How would they come out on the client side? As binary? – Mikael Eriksson Jul 08 '13 at 17:54
1

They would come out on the client side as whatever the underlying type is. Programmatically they would likely be an Object that you would need to explicitly cast. http://stackoverflow.com/questions/13823130/are-there-any-benefits-to-using-sql-variant-over-varchar-in-sql-server – Louis Ricci Jul 08 '13 at 18:01

score 1 · Answer 2 · answered Jul 08 '13 at 17:35

You're creating a column that has 2 separate data types, varchar and datetime. Try casting both possibilities as SQL_VARIANT.

SELECT [DOB] =
CASE
    WHEN [DOB] = '-' THEN CAST('Invalid' as sql_variant)
    ELSE CAST(CAST([DOB] as DATETIME) as sql_variant)
END

http://msdn.microsoft.com/en-us/library/ms173829(v=sql.90).aspx

CASE expression yields error when mixing types

2 Answers2