MySQL DAYOFWEEK() Function

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

Introduction to MySQL DAYOFWEEK() function

The DAYOFWEEK() function allows you to get a weekday index of a date i.e., 1 for Sunday, 2 for Monday, … 7 for Saturday.

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

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

The DAYOFWEEK function accepts a DATE or DATETIME value. It returns an integer that ranges from 1 to 7 that represents Sunday to Saturday.

Note that DAYOFWEEK() function returns an index value based on the ODBC standard where weekdays are indexed with values, with 1 representing Sunday, 2 representing Monday, and so on.

If the date is NULL, zero ( 0000-00-00), or invalid, the DAYOFWEEK function returns NULL.

MySQL DAYOFWEEK() function examples

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

1) Simple DAYOFWEEK function example

The following example uses the DAYOFWEEK() function to return the weekday index of December 1st, 2012:

SELECT 
  DAYNAME('2012-12-01'), 
  DAYOFWEEK('2012-12-01');Code language: JavaScript (javascript)

Output:

+-----------------------+-------------------------+
| DAYNAME('2012-12-01') | DAYOFWEEK('2012-12-01') |
+-----------------------+-------------------------+
| Saturday              |                       7 |
+-----------------------+-------------------------+
1 row in set (0.02 sec)Code language: JavaScript (javascript)

In this query, the DAYNAME function returns the weekday’s name while DAYOFWEEK function returns the weekday index of December 1st, 2012.

2) Using the MySQL DAYOFWEEK() function with table data

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

The following example uses the DAYOFWEEK() function to get the weekday index of the values in the orderDate column:

SELECT 
  orderNumber, 
  orderDate, 
  DAYOFWEEK(orderDate) 
FROM 
  orders;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+------------+----------------------+
| orderNumber | orderDate  | DAYOFWEEK(orderDate) |
+-------------+------------+----------------------+
|       10100 | 2003-01-06 |                    2 |
|       10101 | 2003-01-09 |                    5 |
|       10102 | 2003-01-10 |                    6 |
|       10103 | 2003-01-29 |                    4 |
...Code language: plaintext (plaintext)

Summary

Use the DAYOFWEEK() function to get the weekday index of a date.

Was this tutorial helpful?