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?
select v.name, v.value,
when isdate(v.value) = 1 and v.value is not null and rtrim(ltrim(v.value)) <> '' then cast(v.value as datetime)
from tickets t join vVariablesForTickets v on v.process_id = t.process_id