MySQL Cookbook, 2nd Edition

Finding the Day of the Week for a Date

Problem

You want to know the day of the week on which a date falls.

Solution

Use the DAYNAME() function.

Discussion

To determine the name of the day of the week for a given date, use DAYNAME():

mysql>SELECT CURDATE(), DAYNAME(CURDATE());
+------------+--------------------+
| CURDATE()  | DAYNAME(CURDATE()) |
+------------+--------------------+
| 2006-05-22 | Monday             |
+------------+--------------------+

DAYNAME() is often useful in conjunction with other date-related techniques. For example, to determine the day of the week for the first of the month, use the first-of-month expression from Finding the First Day, Last Day, or Length of a Month as the argument to DAYNAME():

mysql>SET @d = CURDATE();
mysql> SET @first = DATE_SUB(@d,INTERVAL DAYOFMONTH(@d)-1 DAY);
mysql> SELECT @d AS 'starting date',
    -> @first AS '1st of month date',
    -> DAYNAME(@first) AS '1st of month day';
+---------------+-------------------+------------------+
| starting date | 1st of month date | 1st of month day |
+---------------+-------------------+------------------+
| 2006-05-22    | 2006-05-01        | Monday           |
+---------------+-------------------+------------------+