Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Select Case


SQL Select Case

Author
Message
a_car11
a_car11
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
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'
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
a_car11
a_car11
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 18143
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
a_car11
a_car11
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
It gives:
"Error: [SQL0171] Argument 1 of function CASE not valid."
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
a_car11
a_car11
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 46
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'
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8566 Visits: 18143
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search