Get the rows, where day is equal to some value

Question

I have a field with type= date 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

Answer 1

Use the DAYOFMONTH function in the query.

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

Answer 2

you can use DATE_FORMAT method also

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

Answer 3

Another approach would be using like operator:

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

Answer 4

DAYOFMONTH is the way to go.