CODEWITHSUNDEEP
sql
Samuel Mathews
Samuel Mathews

Reputation: 171

sql query working only at times

i need your help on this i need to pull birthdays between two dates irrespective of year i use the below query

 $sql = "SELECT * FROM family_member WHERE DATE_FORMAT(dob, '%c-%d') BETWEEN DATE_FORMAT('2013-".$from_month."-".$from_day."', '%c-%d')  AND DATE_FORMAT('2013-".$to_month."-".$to_day."', '%c-%d') order by MONTH(dob), DAYOFMONTH(dob)";

the query works well if i give the - start date as Nov 6 & - End date as Dec 13

but the query returns zero records if i give - start date as Sept 6 & - end date as Dec 13

it works at certain scenarios. can you please let me know the issue i need to correct

Upvotes: 0

Views: 47

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65294

This is due to the fact, that September is the 9th month and "09" and "9" are not the same string. Use %m instead of %c

Edit

Some explanation as requested: For the month range of September to December your comparison range was 9-06 to 12-06. Now remembering, that this is a string comparison, e.g. 10-25 is NOT bigger than 9-06, meaning the BETWEEN clause will produce no meaningful results. If you chose 2-digit months, you end up comparing 09-06 to 10-25, which works as expected.

Upvotes: 2

Related Questions