My SQL limiting a search by just the month in a where clause

Question

so i need to limit my query by the month 09 in my function but I can't seem to figure out the where portion. i have it written as such

''' select fname, lname, dob as 'Sept Birthdays' '''from student '''where dob = '%09%' '''order by name;

everything I seem to be reading on this site and in my notes doesn't really give me a working answer. as I have the data in the table itself as a yyyy-mm-dd format I just want the mm. any help?

the table set I'm working with is this table set these are all fake names btw so I'm not giving out any information. sorry if this is a duplicate but I cant find the duplicate hence this question.

I think you want `like`, not `=`. See https://www.w3schools.com/sql/sql_like.asp. Also you probably want to be careful about not matching the ninth day or year. — user2740650, Mar 28 '21 at 00:43
If the "dob" field is actually stored as a data field, you can use the MONTH() function. eg. MONTH(dob) = 9. Or if your "dob" field is just a string, then MONTH(DATE(dob)) = 9 should do the trick — Craig, Mar 28 '21 at 00:46
Note SO supports markup (both Markdown and HTML) to automatically format (and highlight) code. When you post (or edit your post), there are buttons to apply such formatting and in-editor help explaining the formatting. Site help should also have pages explaining the markup. Why not try it with this question? — outis, Apr 26 '21 at 23:25
Does this answer your question? [MYSQL select all from table where month?](https://stackoverflow.com/questions/6731854/mysql-select-all-from-table-where-month) — outis, Apr 26 '21 at 23:41

score 1 · Accepted Answer · edited Apr 26 '21 at 23:39

A column that a data is of type date? If so, you can use this query.

I made this query based on yours, anything is just you adapt.

SELECT fname, lname, dob AS 'Sept Birthdays' 
  FROM student 
  WHERE MONTH(dob) = 9 
  ORDER BY name DESC;

ORDER BY can be used with DESC or ASC for descending or ascending order (respectively).

MOUNT(column) = value // for searches by month
YEAR(column) = value // for searches by year
DAY(column) = value // for searches by day

Obs: You can use together for example YEAR and MONTH

Note that, as the column data isn't directly compared but instead is passed through a function, indices on student.dob in general can't be used, which will impact query performance. If this is important, see "Is it possible to have function-based index in MySQL?".

Note though that functions cannot use indexes – Strawberry Mar 28 '21 at 06:10 — Strawberry, Mar 28 '21 at 06:10

My SQL limiting a search by just the month in a where clause

1 Answers1