datetime searching using sub-sections of the YYYY-MM-DD HH:MM string

Question

[MySQL/PHP] My table has a date column of datetime format. All records are of the YYYY-MM-DD HH:MM:SS variety.

MySQL queries like SELECT record FROM table WHERE date > '1941' AND date < '1945' work nicely.

MySQL queries like SELECT record FROM table WHERE date > '1941-03-01' AND date < '1945-01-30' also work nicely.

But what about if I wanted all records that were filed in March, regardless of year? Or all records filed on the 17th, regardless of month/year?

`SELECT record FROM table WHERE date = '03' clearly doesn't work.

I know I could snag it with a LIKE '%-03-%' parameter, but that doesn't leave room for me to search for range, like all records from March to May.

Help? :-)

Answer 1

Try WHERE MONTH(DATE(`date`)) BETWEEN '03' AND '05'

The DATE() part is to extract the date from the timestamp to be used with MONTH().

Answer 2

If you look at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html, you will find many useful functions for your purpose. ... WHERE MONTH(date) = 3, e.g. =)

Answer 3

You can use MySQL date functions:

SELECT record FROM table WHERE MONTH(date) = 3
SELECT record FROM table WHERE DAY(date) = 17

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html