The MySQL DAYOFWEEK() function explained

Learn how DAYOFWEEK() function works in MySQL database server

Posted on October 17, 2021


MySQL DAYOFWEEK function is used to get the weekday number index from a given date value (can be in DATE or DATETIME type).

The number index for the weekday ranges from 1 to 7 with 1 represents Sunday while 7 represents Saturday.

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

Let’s see the DAYOFWEEK() in action.

You need to pass a valid DATE or DATETIME value in the format of YYYY-MM-DD for string type or YYYYMMDD for integer type.

Here’s an example of getting the weekday index from 17th October 2021, which is a Sunday (1):

SELECT DAYOFWEEK("2021-10-17");

--   +-------------------------+
--   | DAYOFWEEK("2021-10-17") |
--   +-------------------------+
--   |                       1 |
--   +-------------------------+

SELECT DAYOFWEEK("20211017");

--   +-----------------------+
--   | DAYOFWEEK("20211017") |
--   +-----------------------+
--   |                     1 |
--   +-----------------------+

For a DATETIME or TIMESTAMP value, you need to pass the argument in the format of YYYY-MM-DD hh:mm:ss for string type or YYYYMMDDhhmmss for integer type.

Here’s another example of getting the weekday index of 16th October 2021, which is Saturday (7):

SELECT DAYOFWEEK("2021-10-16 13:00:00");

--   +----------------------------------+
--   | DAYOFWEEK("2021-10-16 13:00:00") |
--   +----------------------------------+
--   |                                7 |
--   +----------------------------------+

SELECT DAYOFWEEK("20211016130000");

--   +-----------------------------+
--   | DAYOFWEEK("20211016130000") |
--   +-----------------------------+
--   |                           7 |
--   +-----------------------------+

The function will return NULL values when you pass a DATE or DATETIME with invalid format or any other value that can’t be interpreted as a date.

Take a look at the following example query:

SELECT DAYOFWEEK("2021-17-9"); -- NULL

SELECT DAYOFWEEK("Hello"); -- NULL

SELECT DAYOFWEEK(77); -- NULL

SELECT DAYOFWEEK(false); -- NULL

The DAYOFWEEK() function is similar to the WEEKDAY() function.

Both returns a number index for the specified weekday, but the difference is that the DAYOFWEEK() function starts from 1 to 7 and from Sunday to Saturday.

Meanwhile, the WEEKDAY() function starts from 0 to 6 and from Monday to Sunday.

# DAYOFWEEK          # WEEKDAY
1 = Sunday           0 = Monday
2 = Monday           1 = Tuesday
3 = Tuesday          2 = Wednesday 
4 = Wednesday        3 = Thursday   
5 = Thursday         4 = Friday  
6 = Friday           5 = Saturday
7 = Saturday         6 = Sunday  

You need to be careful not to mix the functions together because they return different numbers for the same day.

Finally, since the DAYOFWEEK() function can accept DATE, DATETIME, or TIMESTAMP type, you don’t need to format your table column(s) before passing them to the function.

Suppose you have a table called member with the following data:

  • A join_date column as DATE type
  • A last_update column as TIMESTAMP type
+------------+---------------------+
| join_date  | last_update         |
+------------+---------------------+
| 2021-10-15 | 2021-10-19 09:20:10 |
| 2021-10-16 | 2021-10-19 09:20:10 |
+------------+---------------------+

You can pass both columns above to the DAYOFWEEK() function without formatting at all:

SELECT DAYOFWEEK(join_date), DAYOFWEEK(last_update) FROM members;

The returned output will be as follows:

+----------------------+------------------------+
| DAYOFWEEK(join_date) | DAYOFWEEK(last_update) |
+----------------------+------------------------+
|                    6 |                      3 |
|                    7 |                      3 |
+----------------------+------------------------+

Now you have learned how the DAYOFWEEK function works. Great job! 👍

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.