October 3, 2013 at 9:04 am
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'
October 3, 2013 at 9:13 am
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/
October 3, 2013 at 9:25 am
Hi,
Thanks for your replay, but it still returns empty values for all results even when I have data in the "date" ( the column name "date" I added here for simplicity ) column. May be I need a "else" do default to the true value?
Thanks
October 3, 2013 at 9:29 am
a_car11 (10/3/2013)
Hi,Thanks for your replay, but it still returns empty values for all results even when I have data in the "date" ( the column name "date" I added here for simplicity ) column. May be I need a "else" do default to the true value?
Thanks
When you have no else this would return NULL.
_______________________________________________________________
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/
October 3, 2013 at 10:01 am
You need to add an ELSE to your CASE statement. I'm sure you don't need separate conditions even if it becomes more readable.
SELECT name,
address,
email,
CASE
WHEN date = '0001-01-01'
OR date is null
THEN ''
ELSE date
END as date
FROM mytable
WHERE name = 'john'
I would like to reinforce the comments made by Sean about using the correct data types. You might not see it at the beginning but they'll become a PITA.
October 3, 2013 at 11:18 am
It gives:
"Error: [SQL0171] Argument 1 of function CASE not valid."
October 3, 2013 at 12:06 pm
a_car11 (10/3/2013)
It gives:"Error: [SQL0171] Argument 1 of function CASE not valid."
Are you using sql server? That is not an error I have seen in SQL server.
What is the actual code you are running? We can't provide very decent help without knowing what the query looks like.
_______________________________________________________________
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/
October 7, 2013 at 7:02 am
To answer my own question, this worked:
SELECT name,
address,
email,
CASE
WHEN date = '0001-01-01'
OR date is null
THEN ''
ELSE varchar(date,iso)
END as date
FROM mytable
WHERE name = 'john'
October 7, 2013 at 7:44 am
And that's why you should post DDL with your questions. And as this is a SQL Server site, you should tell us that you're not using SQL Server :-D.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply