The MySQL WEEKDAY() function returns the index number of the day from a DATE or DATETIME string or integer.
The index number starts from 0 for Monday and ends with 6 for Sunday.
The WEEKDAY() function syntax is as follows:
WEEKDAY(DATE/DATETIME)
The parameter DATE format must be in the form of YYYY-MM-DD for string type or YYYYMMDD for integer type.
If you’re passing a DATETIME format, then it must be YYYY-MM-DD hh:mm:ss for string type or YYYYMMDDhhmmss for integer type.
Here’s a SELECT statement example that makes use of the WEEKDAY() function. Note that the first function call uses the string format and the second one uses integer format:
SELECT WEEKDAY('2021-09-25'), WEEKDAY(20210925);
Since 25th September 2021 (today) is Saturday, the WEEKDAY function returns 5:
+-----------------------+-------------------+
| WEEKDAY('2021-09-25') | WEEKDAY(20210925) |
+-----------------------+-------------------+
| 5 | 5 |
+-----------------------+-------------------+
1 row in set (0.00 sec)
While you can pass a DATETIME format, the time section usually has no effect because the WEEKDAY() function only counts the date section.
But this means you don’t have to format a DATETIME column into a DATE column before passing it to the WEEKDAY() function.
For example, suppose you have a members table with the following data:
+------------+---------------------+
| join_date | last_update |
+------------+---------------------+
| 2021-09-22 | 2021-09-22 09:20:10 |
| 2021-09-23 | 2021-09-23 09:20:10 |
| 2021-09-24 | 2021-09-24 09:20:10 |
| 2021-09-25 | 2021-09-25 09:20:10 |
+------------+---------------------+
4 rows in set (0.00 sec)
You can pass both columns to WEEKDAY() function call without having any error:
SELECT WEEKDAY(join_date), WEEKDAY(last_update) FROM members;
The SQL query above will produce the following result set:
+--------------------+----------------------+
| WEEKDAY(join_date) | WEEKDAY(last_update) |
+--------------------+----------------------+
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+--------------------+----------------------+
4 rows in set (0.00 sec)
The index number 2 equals Wednesday, 3 equals Thursday, and so on.
When you pass an invalid date format, then the function will return NULL.
The example below shows a WEEKDAY() function call with MM-DD-YYYY format:
mysql> SELECT WEEKDAY('09-25-2021');
+-----------------------+
| WEEKDAY('09-25-2021') |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
And that’s how MySQL WEEKDAY() function works 😉