MySQL WEEKDAY() Function

Summary: in this tutorial, you will learn how to use the MySQL WEEKDAY() function to get the weekday index for a specific date.

Introduction to MySQL WEEKDAY() function

The WEEKDAY() function returns a weekday index for a date i.e., 0 for Monday, 1 for Tuesday, … 6 for Sunday.

Here’s the syntax of the WEEKDAY() function:

WEEKDAY(date)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• date: This is the date value that you want to get the weekday from. The date can be a DATE or DATETIME value.

The WEEKDAY() function returns an integer that represents from Monday to Sunday. Also, it returns NULL if the date is NULL, invalid, or zero ( 0000-00-00).

MySQL WEEKDAY() examples

Let’s take some examples of using the WEEKDAY() function.

1) Simple WEEKDAY() function example

The following example uses the WEEKDAY() function to get the weekday index for the date '2010-01-01':

SELECT 
  DAYNAME('2010-01-01'), 
  WEEKDAY('2010-01-01');Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------+-----------------------+
| DAYNAME('2010-01-01') | WEEKDAY('2010-01-01') |
+-----------------------+-----------------------+
| Friday                |                     4 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

In this example, we use the DAYNAME() function to get the weekday’s name and WEEKDAY() function to get the weekday index of January 1st, 2010.

2) Using the WEEKDAY() function with table data

We’ll use the orders table from the sample database:

The following example uses WEEKDAY() function to count the number of orders placed on Monday in 2004:

SELECT 
  COUNT(*) 
FROM 
  orders 
WHERE 
  WEEKDAY(orderDate) = 0 
  AND YEAR(orderDate) = 2004;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

How the query works.

• SELECT COUNT(*): This SELECT clause returns row counts using the COUNT() function.
• FROM orders: This FROM clause specifies the orders table to retrieve the data.
• WHERE WEEKDAY(orderDate) = 0 AND YEAR(orderDate) = 2004: The WHERE clause has two conditions:
  • WEEKDAY(orderDate) = 0: This condition selects rows where the orderDate falls on a Monday (0).
  • YEAR(orderDate) = 2004: This condition checks that the year of the orderDate is equal to 2004.

Summary

• Use the WEEKDAY() function to get the weekday index of a specific date.