0

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,

3
Contributors
3
Replies
22
Views
2 Years
Discussion Span
Last Post by AleMonteiro
0

-- 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?

0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.