What data type to use for days of week (i.e. Sunday, Monday etc) in MySQL?

I need week days in my tables and I need a query to compare with today.

Something like this:

开发者_JS百科$query = "select course_ID from course where day = (today)";  

This is what I want (day = (today)";) and I need the data type for storing week days in tables.

Use the DAYOFWEEK function to map from a date to the day of the week.

Maybe something along the lines of:

$query = "select course_ID from course where dayofweek(some_field) = dayofweek(now())";  

SELECT course_ID FROM course WHERE DATE_FORMAT(date_field_name, '%Y-%m-%d') = CURDATE()

This will do the work no matter if the day is the same year or not.

If you want to match against weekday

SELECT course_ID FROM course WHERE DATE_FORMAT(date_field_name, '%W') = DATE_FORMAT(NOW(), '%W');

Last edit

To match data in day_column if the values are Monday ...Sunday

SELECT course_ID FROM course WHERE day_column = DATE_FORMAT(NOW(), '%W');

You can use an ENUM datatype and have it predefined with the 7 days. See here for more information : http://dev.mysql.com/doc/refman/5.0/en/enum.html

Since dayoftheweek ( as suggested by @borrible) returns numbers, it will be convenient to use this function to compare dates and the days you stored.

I suggested you store the days in numeric data type such as TINYINT.