SQL Question / Stuck !!

[olegb]

SQL Gurus, help !!

I have a table with 3 fields, ID, Answer_5, Answer_6. Answer_5 & Answer_6 both hold a date value, but the fields are defined as string (for other reasons). In Answer_6 it is possible to have the string of 'Now'. I am trying to do a simple select as follows, changing fields on the fly to make 'Now' equal today's date:

select id, answer_5,
CASE  
   when answer_6 = 'Now' THEN cast(GETDATE() as date)
   when answer_6 is NULL then cast('01/1990' as date)
ELSE cast(answer_6 as date) 
END AS new_answer_6 
from tDataMult

I get "Conversion failed when converting date and/or time from character string." when I run and I can't seem to figure out why. Any help is greatly appreciated.

Thanks,

[olegb]

-- quick update, which might be the source of the issue:

In both fields, when the date is stored, it is stored as: mm/yyyy format. How can that be converted to datetime?

[moshelilin]

Add "01/" to the left of your mm/yyyy string.
After this , convert to dateTime.

[AleMonteiro]

I'd go with something like this:

SELECT
    id, answer_5,
    CASE  
       WHEN answer_6 = 'Now' THEN cast(GETDATE() as date)
       WHEN answer_6 IS NULL THEN cast('1990-01-01' as date)
    ELSE cast(answer_6 as date) 
    END AS new_answer_6 
FROM tDataMult