Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Select Case Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 11:48 AM
Points: 4, Visits: 38
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'
Post #1501237
Posted Thursday, October 3, 2013 9:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 13,083, Visits: 11,917
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)
Post #1501242
Posted Thursday, October 3, 2013 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 11:48 AM
Points: 4, Visits: 38
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
Post #1501248
Posted Thursday, October 3, 2013 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 13,083, Visits: 11,917
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)
Post #1501251
Posted Thursday, October 3, 2013 10:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 3,342, Visits: 7,210
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1501267
Posted Thursday, October 3, 2013 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 11:48 AM
Points: 4, Visits: 38
It gives:
"Error: [SQL0171] Argument 1 of function CASE not valid."
Post #1501285
Posted Thursday, October 3, 2013 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 13,083, Visits: 11,917
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)
Post #1501311
Posted Monday, October 7, 2013 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 11:48 AM
Points: 4, Visits: 38
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'

Post #1502112
Posted Monday, October 7, 2013 7:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 3,342, Visits: 7,210
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 .


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502136
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse