Get rows where day is equal to some value

Question

I have a field with type = datein MySQL DB.

It stores dates in a format YY-m-d.

How can I write a query that will receive strings where the day is equal to some value, for example 1?
I need to get only the first months of the months ( 2009-11-01, 2009-12-01, 2010-01-01...)

thank

Answer 1

Use DAYOFMONTH in the query.

SELECT <columns> FROM <table> WHERE DAYOFMONTH(<the-date-column>) = 1

Answer 2

you can use the DATE_FORMATmethod also

SELECT <columns> FROM <table> WHERE DATE_FORMAT(date, "%d") = '1'

Answer 3

like:

select <columns> from <table> where <the-date-column> like  '%-01'

Answer 4

DAYOFMONTH - .