Sql query between two dates without year for zodiac?-CodePudding

I have columns Name and BirthDate with data eg: 1999-10-06.

I need query for Libra: September 23 - October 22, how to do this? I dont need the year.

Expecting something like this but not working:

$query = "SELECT * FROM persons WHERE BirthDate BETWEEN '%9-23' AND '-22' ";

CodePudding user response：

% is only a wildcard when used with LIKE, which isn't helpful here. You can do something like

where case month(BirthDate) when 9 then day(BirthDate) >= 23 when 10 then day(BirthDate) <= 22 end;

CodePudding user response：

You should use build-in function dayofyear

SELECT * 
FROM dates 
WHERE DAYOFYEAR(birthDate) 
   between DAYOFYEAR(CONCAT(year(birthDate),"-09-23")) 
      AND DAYOFYEAR(CONCAT(year(birthDate),"-10-22"))

Live:

http://sqlfiddle.com/#!9/b1625c/8