How to write a query for zodiac using case in mySQL

Question

Display the student name, date of birth and their zodiac sign. Use Zodiac as alias

a. Aries  Mar 21-Apr 19

b. TaurusApr 20 –May 20

c. GeminiMay 21-Jun 20

d. Cancer  Jun21- Jul22

e. Leo  Jul 23- Aug 22

f. Virgo  Aug 23-Sept 22

g. Libra  Sept 23-Oct 22

h. Scorpio  Oct 23- Nov 21

i. Sagittarius  Nov 22-Dec 21

j. Capricorn  Dec 22- Jan 19

k. Aquarius Jan 20 – Feb 18

l. Pisces Feb 19- Mar 20

The code I wrote was:

select sname, sdob, case  
when sdob between (select date('____-03-21')) and (select date('____-04-19')) then 'Aries'
when sdob between '%-04-20' and '%-05-20' then 'Tarus'
when sdob between '%-05-21' and '%-06-20' then 'Gemini'
when sdob between '%-06-21' and '%-07-19' then 'Cancer'
when sdob between '%-07-21' and '%-08-19' then 'Leo'
when sdob between '%-08-21' and '%-09-19' then 'Virgo'
when sdob between '%-09-21' and '%-10-19' then 'Libra'
when sdob between ( sdob like'%-10-21') and ( sdob like'%-11-19') then 'Scropio'
when sdob between ( sdob like'%-11-21') and ( sdob like'%-12-19') then 'Sagittarious'
when sdob between ( sdob like'%-12-21') and ( sdob like'%-01-19') then 'Capricon'
when sdob between '____-01-21' and '____-02-19' then 'Aquarius'
when sdob between '%-02-21' and '%-03-19' then 'Pisces'
end 'SunSign'
from student;

But it shows all null values in the SunSign field on the output.

Joseph B Joseph B · Accepted Answer · 2014-07-08T12:22:53

If sdob is a date column, then the MONTH and DAYOFMONTH functions can be used with the CASE statement, as below:

select sname, sdob, 
case  
WHEN (MONTH(sdob) = 3 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 4 AND DAYOFMONTH(sdob) <= 19) THEN 'Aries'
WHEN (MONTH(sdob) = 4 AND DAYOFMONTH(sdob) >= 20) OR (MONTH(sdob) = 5 AND DAYOFMONTH(sdob) <= 20) THEN 'Taurus'
WHEN (MONTH(sdob) = 5 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 6 AND DAYOFMONTH(sdob) <= 20) THEN 'Gemini'
WHEN (MONTH(sdob) = 6 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 7 AND DAYOFMONTH(sdob) <= 20) THEN 'Cancer'
WHEN (MONTH(sdob) = 7 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 8 AND DAYOFMONTH(sdob) <= 20) THEN 'Leo'
WHEN (MONTH(sdob) = 8 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 9 AND DAYOFMONTH(sdob) <= 20) THEN 'Virgo'
WHEN (MONTH(sdob) = 9 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 10 AND DAYOFMONTH(sdob) <= 20) THEN 'Libra'
WHEN (MONTH(sdob) = 10 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 11 AND DAYOFMONTH(sdob) <= 20) THEN 'Scorpio'
WHEN (MONTH(sdob) = 11 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 12 AND DAYOFMONTH(sdob) <= 20) THEN 'Sagittarius'
WHEN (MONTH(sdob) = 12 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 1 AND DAYOFMONTH(sdob) <= 20) THEN 'Capricorn'
WHEN (MONTH(sdob) = 1 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 2 AND DAYOFMONTH(sdob) <= 20) THEN 'Aquarius'
WHEN (MONTH(sdob) = 2 AND DAYOFMONTH(sdob) >= 21) OR (MONTH(sdob) = 3 AND DAYOFMONTH(sdob) <= 20) THEN 'Pisces'
end 'SunSign'
from student;

Please note that there were some gaps in the ranges (e.g. between Libra and Scorpio), which I have closed, by changing the date limits slightly.

How to write a query for zodiac using case in mySQL

1 Answers