IF/ELSIF program in SQL

Chinese Zodiac question:

I have to write an SQL program which I have to find what animal is on my birthyear.  I need to declare a date variable birth_date and assign it to my birthdate.  Use an IF/ELSIF structure to test every year and determine the animal associated with my birth year.  
1924.36.48.60.72.84.96 - Rat
1925.37.49.61.73.85.97 - Cow
1926.38.50.62.74.86.98 - Tiger
1927.39.51.63.75.87.99 - Rabbit
1928.40.52.64.76.88.00 - Dragon
1929.41.53.65.77.89.01 - Snake
...................... - Horse
...................... - Sheep
...................... - Monkey
...................... - Chicken
...................... - Dog
1935.47.59.71.83.95.07 - Pig

output should be this
dbms_output.put_line('I was born in ' || year || ', which is the year of the ???') ??? = whatever animal is in that year.
leegarsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Are you using a SQL Server database?  If not, the statement below will almost certainly not work.  However, the LOGIC of how to determine the animal should work in any database/language, if you convert the SYNTAX.


DECLARE @birth_date SMALLDATETIME
SET @birth_date = 'Aug 17, 1975' --or whatever
SELECT 'I was born in ' + CAST(YEAR(@birth_date) AS CHAR(4)) + ' which is the year of the ' +
CASE (YEAR(@birth_date) - 1924) % 12
WHEN 0 THEN 'Rat'
WHEN 1 THEN 'Cow'
WHEN 2 THEN 'Tiger'
--...
WHEN 11 THEN 'Pig'
END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Note that you could of course easily go back to dates prior to 1924 if you want to: just back up 1924 by even multiples of 12 years, the rest of the logic should function perfectly.

By the way, % is the modulo (remainder) operator in SQL Server; on other systems it is MOD or \ or whatever else.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.