MySQL WEEKDAY() function explained

Let's learn how MySQL WEEKDAY() function works

Posted on September 25, 2021


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 😉

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.