Conversion failed when converting date and/or time from character string

The following query returns an error "Conversion failed when converting date and/or time from character string". However, if I changed "else v.value" to "else null" or remove the else, it worked fine. Why the else caused error on when? What is the work around?

Thanks.

select v.name, v.value,
case
when isdate(v.value) = 1 and v.value is not null and rtrim(ltrim(v.value)) <> '' then cast(v.value as datetime)
else v.value
end
from tickets t join vVariablesForTickets v on v.process_id = t.process_id

The error is because the data type of the column is datetime, because of "cast(v.value as datetime)".

But in the else, you're including v.value even though it's not a valid date. When SQL tries to convert the value that failed the when condition, it will get an error.

Possible work-arounds are:

  1. make the column datetime and list only good values in that column, have another column for invalid values
  2. make the column varchar and convert valid dates to a specific format
  3. make the column "sql_variant", and list both types of columns, datetime and varchar, in the same column

Thanks. I do validate the data in when isdate(v.value) = 1.
I tried to cast the else but got the same error. Can you please modify my query? I would like to see the syntax. Thanks.

select v.name, v.value,
case
when isdate(v.value) = 1 and v.value is not null and rtrim(ltrim(v.value)) <> '' then cast(v.value as datetime)
else cast(v.value as varchar(1000))
end
from tickets t join vVariablesForTickets v on v.process_id = t.process_id

A given column can have only one data type. As I stated before, when you specified "cast(v.value as datetime)", the data type of the result column became datetime, since datetime has a higher precedence than varchar ["cast(v.value as varchar(1000)"].

The column can't be both datetime or varchar, it can only be one of them. You can't tell SQL a column is datetime and then try to load 'abcd' into it.

As I said, the only way you can "mix" the types is to make the column "sql_variant", which explicitly tells SQL that you in fact will have different types of data in the column.

select v.name, v.value,
case
when isdate(v.value) = 1 and v.value is not null and rtrim(ltrim(v.value)) <> '' then cast(cast(v.value as datetime) as sql_variant)
else cast(cast(v.value as varchar(1000)) as sql_variant)
end
from tickets t join vVariablesForTickets v on v.process_id = t.process_id

It works. Thank you very much.