MySQL WEEKDAY() function explained

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 😉

More MySQL tutorials