Get the rows, where day is equal to some value

I have a field with type= da开发者_运维百科te in MySQL DB.

It store dates in YY-m-d format.

How can I write a query which will get the rows where day is equal to some value, for example 1?

I need to get only first days of months (2009-11-01, 2009-12-01, 2010-01-01...)

Thanks

Use the DAYOFMONTH function in the query.

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

you can use DATE_FORMAT method also

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

DAYOFMONTH is the way to go.

Another approach would be using like operator:

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