MySQL WEEKDAY() function

Last update on March 14 2026 12:35:49 (UTC/GMT +8 hours)

WEEKDAY() function

MySQL WEEKDAY() returns the index of the day in a week for a given date (0 for Monday, 1 for Tuesday and ......6 for Sunday).

This function is useful in -

WEEKDAY() is used to allocate resources efficiently based on the nature of the tasks and the availability of resources on specific weekdays.
It's used to trigger alerts or notifications based on specific weekdays, allowing for the automation of certain tasks or reminders.
WEEKDAY() is helpful for segmenting or categorizing data by weekdays, which can be valuable in various reporting and analysis scenarios.
It's used in event planning to schedule events on days that are most convenient or likely to attract a larger audience.
WEEKDAY() can be used in predictive analysis to forecast trends or behaviors that are influenced by specific weekdays.
For businesses, it can be used to identify which days of the week experience the highest or lowest levels of activity.
In logistics and delivery systems, WEEKDAY() helps in planning routes and deliveries based on factors like traffic patterns on specific weekdays.

Syntax:

WEEKDAY(date)

Where date is a date.

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL WEEKDAY() function

The following statement will return the index of the week for the date 2009-05-19.

Code:

SELECT WEEKDAY('2009-05-19');

Output:

mysql> SELECT WEEKDAY('2009-05-19');
+-----------------------+
| WEEKDAY('2009-05-19') |
+-----------------------+
|                     1 | 
+-----------------------+
1 row in set (0.00 sec)

Example: WEEKDAY() function using on table

The following statement will return the invoice_no, receive_dt and index of the week for receive_dt, after making sure that index of the week for receive_dt must be more than 0 and less than 6 (i.e. it should be from Monday through Saturday).

Sample table: purchase

+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| invoice_no | invoice_dt | ord_no         | ord_date   | receive_dt | book_id | book_name                       | pub_lang | cate_id | receive_qty | purch_price | total_cost |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| INV0001    | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001   | Introduction to Electrodynamics | English  | CA001   |          15 |       75.00 |    1125.00 |
| INV0002    | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |           8 |       55.00 |     440.00 |
| INV0003    | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005   | Conceptual Physics              | NULL     | CA001   |          20 |       20.00 |     400.00 |
| INV0004    | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |          15 |       35.00 |     525.00 |
| INV0005    | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001   | Introduction to Electrodynamics | English  | CA001   |           8 |       25.00 |     200.00 |
| INV0006    | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003   | Guide to Networking             | Hindi    | CA003   |          20 |       45.00 |     900.00 |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+

Code:

 SELECT invoice_no,receive_dt,WEEKDAY(receive_dt)
      FROM purchase           
      WHERE WEEKDAY(receive_dt)>0 
      AND WEEKDAY(receive_dt)<6;

Output:

mysql> SELECT invoice_no,receive_dt,WEEKDAY(receive_dt)
    ->       FROM purchase           
    ->       WHERE WEEKDAY(receive_dt)>0 
    ->       AND WEEKDAY(receive_dt)< 6;
+------------+------------+---------------------+
| invoice_no | receive_dt | WEEKDAY(receive_dt) |
+------------+------------+---------------------+
| INV0001    | 2008-07-19 |                   5 | 
| INV0002    | 2008-08-28 |                   3 | 
| INV0003    | 2008-09-23 |                   1 | 
| INV0004    | 2007-08-30 |                   3 | 
+------------+------------+---------------------+
4 rows in set (0.06 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

PREV : WEEK()
NEXT : WEEK OF YEAR()