sorting dates in MySQL fields

[Anon]

I've got a news backend that stores the date of a news story in a MySQL field as mm/dd/yy, such as 11/30/00. What I want to know is, how can I search for rows by their date? Say I wanted to search for news stories that were created in the January or in the year 2000, is it possible to search only a portion of a MySQL cell?

[Anon]

show all dates in january

SELECT * FROM table WHERE date_field LIKE '01-%';

show all dates in the year 2000

SELECT * FROM table WHERE date_field LIKE '%-%-00';

this solution is based on the actual ordering of the dates in mySQL and won't work if you switch databases as they store dates in a different order in Postgresql and oracle. there's a better way to do it that uses an included sql function that i dont remember off the top of my head right now that is a better way to do it but this will work. it would look something like this:

get all of january:

SELECT * FROM table WHERE GETMONTH(date_field) = '01';

that would be more portible but i forget the exact syntax. look at your database's documentation.

-harlan

[Anon]

You should use 'date' field type, and the SQL query would look something like this:

SELECT * FROM table WHERE MONTH(field) = '01'
Returns everything of januar.

You can also use YEAR(field) and DAYOFMONTH(field) etc.

• Hjalti

[Anon]

yeah that's what i was looking for

i knew there were some built in conversion routines to make the 'date' datatype spit out only the month or day or year

-harlan