How to use the MySQL DAYOFWEEK() function

The DAYOFWEEK() is a MySQL function that returns the weekday index for a given date. The days are indexed starting with 1 for Sunday through 7 for Saturday.

Syntax

The syntax for DAYOFWEEK() is:

DAYOFWEEK(date)

Where date is a DATE or DATETIME value.

Examples

Here are some examples of using DAYOFWEEK() in MySQL queries:

SELECT DAYOFWEEK('2023-11-14');

-- Returns 3 (Tuesday)

Get the weekday index for the specific date ‘2023-11-14’.

SELECT name, DAYOFWEEK(birthday)
FROM users;

Retrieve the name and weekday of birth date for all users.

SELECT name
FROM users
WHERE DAYOFWEEK(birthday) = 1;

Get names of users with birthdays on a Sunday.

SELECT DATE_FORMAT(NOW(), '%w') = DAYOFWEEK(NOW());

-- Returns 1 (true)

Compare weekday index of current date to value returned by DAYOFWEEK().

SELECT DATE_ADD(NOW(), INTERVAL (8 - DAYOFWEEK(NOW())) DAY);

-- Returns 2023-11-21 00:00:00

Use DAYOFWEEK() to calculate the next Tuesday from the current date.

Other Date Functions

Other handy MySQL date functions:

• DAYOFMONTH() - Extract day of month from 1-31
• DAYOFYEAR() - Get numeric day of the year
• WEEKDAY() - Weekday name from date
• QUARTER() - Quarter of year from date
• WEEK() - Week number in year for date

DAYOFWEEK() can be used together with these to get or process parts of a date.