How to use the MySQL WEEKDAY() function

In this article, we will learn how to use the WEEKDAY() function in MySQL. The WEEKDAY() function returns the index of the weekday for a given date, where Monday is 0 and Sunday is 6. This function can be useful for performing calculations or comparisons based on the day of the week.

Syntax

The syntax of the WEEKDAY() function is as follows:

WEEKDAY(date)

The date parameter can be any valid date or datetime expression. If the date is not a valid date, the function returns NULL.

Examples

Let’s see some examples of how to use the WEEKDAY() function in MySQL.

Example 1: Get the weekday index for a specific date

The following query returns the weekday index for the date ‘2023-12-25’, which is a Monday.

SELECT WEEKDAY('2023-12-25') AS weekday;

The output is:

+---------+
| weekday |
+---------+
|       0 |
+---------+

Example 2: Get the weekday name for a specific date

We can use the DAYNAME() function to get the weekday name for a specific date. For example, the following query returns the weekday name for the date ‘2023-12-25’, which is ‘Monday’.

SELECT DAYNAME('2023-12-25') AS weekday_name;

The output is:

+--------------+
| weekday_name |
+--------------+
| Monday       |
+--------------+

Example 3: Get the number of weekdays in a month

We can use the WEEKDAY() function to count the number of weekdays in a month. For example, the following query returns the number of Mondays in December 2023.

SELECT COUNT(*) AS mondays
FROM (
  SELECT DATE('2023-12-01') + INTERVAL n DAY AS date
  FROM (
    SELECT a.N + b.N * 10 + c.N * 100 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS c
  ) AS t
  WHERE n BETWEEN 0 AND 30
) AS t
WHERE WEEKDAY(date) = 0;

The output is:

+---------+
| mondays |
+---------+
|       4 |
+---------+

Example 4: Get the first and last weekday of a month

We can use the WEEKDAY() function to get the first and last weekday of a month. For example, the following query returns the first and last Monday of December 2023.

SELECT MIN(date) AS first_monday, MAX(date) AS last_monday
FROM (
  SELECT DATE('2023-12-01') + INTERVAL n DAY AS date
  FROM (
    SELECT a.N + b.N * 10 + c.N * 100 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS c
  ) AS t
  WHERE n BETWEEN 0 AND 30
) AS t
WHERE WEEKDAY(date) = 0;

The output is:

+--------------+-------------+
| first_monday | last_monday |
+--------------+-------------+
| 2023-12-04   | 2023-12-25  |
+--------------+-------------+

Related Functions

Some of the related functions that can be used with the WEEKDAY() function are:

• DAYOFWEEK(): Returns the index of the weekday for a given date, where Sunday is 1 and Saturday is 7.
• DAYNAME(): Returns the name of the weekday for a given date.
• WEEK(): Returns the week number for a given date, based on a specified mode.
• WEEKOFYEAR(): Returns the week number for a given date, based on the ISO 8601 standard.
• WEEKDAYNAME(): Returns the name of the weekday for a given weekday index, where Monday is 0 and Sunday is 6.

Conclusion

In this article, we learned how to use the WEEKDAY() function in MySQL. We saw some examples of how to get the weekday index, name, count, and range for a given date or month. We also learned about some related functions that can be used with the WEEKDAY() function.