Finding the Day of the Week for a Date

5.25.1 Problem

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

5.25.2 Solution

Use the DAYNAME( ) function.

5.25.3 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( )) |
+------------+--------------------+
| 2002-07-15 | Monday |
+------------+--------------------+

DAYNAME( ) is often useful in conjunction with other date-related techniques. For example, to find out the day of the week for the first of the month, use the first-of-month expression from earlier in the chapter 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 |
+---------------+-------------------+------------------+
| 2002-07-15 | 2002-07-01 | Monday |
+---------------+-------------------+------------------+