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_datecolumn asDATEtype - A
last_updatecolumn asTIMESTAMPtype
+------------+---------------------+
| 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! 👍