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.