a_car11 (10/3/2013)
I am trying to replace a value from my select if the value from this column 'date' is equal to '0001-01-01' to ''. Not working, it returns nothing to all fields, even when there is data in the 'date' field.select name,address, email,
case
when date = '0001-01-01'
or date is null
then ''
END as date
FROM mytable
WHERE name = 'john'
It would require two separate conditions.
select name,address, email,
case
when date = '0001-01-01' then ''
when date is null then ''
END as date
FROM mytable
WHERE name = 'john'
Now that being clarified, you should use proper datatypes. When you store date information you should use datetime datatypes instead of character datatypes.
Also you should avoid naming your columns using reserved words like "date". Not only is that a pain to deal with, your name should give you a clue what it means. Date by itself is meaningless. If you name your column something like DateCreated or BirthDate it gives it some meaning.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/