How to Use the WEEKDAY() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the WEEKDAY() function in MySQL.

Using the WEEKDAY() function in MySQL

In MySQL, the WEEKDAY() function is used to retrieve the weekday index (0 for Monday, 1 for Tuesday, etc.) for a specified date. The function returns an integer value that represents the day of the week.

Syntax

WEEKDAY(date);

Examples

Here are some examples of using the WEEKDAY() function in MySQL:

Extracting the weekday from a date string

To extract the weekday from a date string, you can pass the string as an argument to the WEEKDAY() function.

Syntax
SELECT WEEKDAY('2022-05-15');
Example
SELECT WEEKDAY('2022-05-15');

Output:

+------------------+
| WEEKDAY('2022-05-15') |
+------------------+
|                6 |
+------------------+

In this example, the date ‘2022-05-15’ corresponds to a Sunday, which has an index of 6 (since 0 corresponds to Monday).

Extracting the weekday from a date column in a table

To extract the weekday from a date column in a table, you can use the WEEKDAY() function in the SELECT statement.

Syntax
SELECT WEEKDAY(date_column) FROM table_name;
Example

Consider a table orders with columns order_id, order_date, and order_total. To extract the weekday index from the order_date column, you can use the following query:

SELECT WEEKDAY(order_date) FROM orders;

Output:

+-------------------+
| WEEKDAY(order_date) |
+-------------------+
|                  2 |
|                  5 |
|                  1 |
|                  3 |
+-------------------+

Using WEEKDAY() function with WHERE clause

You can also use the WEEKDAY() function in a WHERE clause to filter records based on the weekday index.

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE WEEKDAY(date_column) = weekday_index;
Example

To select orders placed on a Tuesday (weekday index 2), you can use the following query:

SELECT order_id, order_total
FROM orders
WHERE WEEKDAY(order_date) = 2;

Output:

+----------+-------------+
| order_id | order_total |
+----------+-------------+
|     1001 |       250.00 |
|     1003 |      1345.00 |
+----------+-------------+
#MySQL