MySQL – Get Day of Week Name Using DAYOFWEEK()

In SQL language. The DAYOFWEEK() function is used to get the week day of a specific date. The result is a numerical index from 1 (Sunday) to 7 (Saturday).

Detail of possible results:

• 1 : Sunday
• 2 : Monday
• 3 : Tuesday
• 4 : Wednesday
• 5 : Thursday
• 6 : Friday
• 7 : Saturday

 
 

Syntax:

SELECT DAYOFWEEK(column_date) FROM table

In this example, the result will be a list of weekdays for the date in “column_date”.
 

Example 1: Getting the current day of the week:

A very simple example is to try to get the day of the week of the current day, using the following SQL query:

SELECT DAYOFWEEK(NOW());

The result will be a numerical value from 1 to 7.

It is also possible to get the day of the week of a specific date, using an SQL query similar to example 1. For example, to find out the day of the week of September 12, 2022, use the following SQL query:

SELECT DAYOFWEEK('2022-09-12');

Output:

2

September 12, 2018 is Monday (Monday = 2).
 
 

Example 2: Get the day of the week of a date:

Let’s take the example of a table listing users, with the registration date for each person.

+----------+---------+--------+-------------------+-------------------------------+
|  UserID  |  Name   |  age   | registration_date |             Address           |
+----------+---------+--------+-------------------+-------------------------------+
|    101   |  Alex   |   25   | 2022-09-12        | 819 Saint Francis Way         |
|    102   |  Emily  |   15   | 2022-01-12        | 171 Jarvisville Road Michigan |
|    103   |  Jean   |   35   | 2022-01-22        | 188 Clay Street Indiana       |
|    104   |  Bob    |   40   | 2022-02-01        | 285 Java Lane Missouri        |
+----------+---------+--------+-------------------+-------------------------------+

It is possible to determine the day of the week for each registration dates, using the following SQL query:

SELECT UserID, Name, age, registration_date, DAYOFWEEK(registration_date) AS registration_day
FROM users

Output:

+----------+---------+--------+-------------------+-------------------------+
|  UserID  |  Name   |  age   | registration_date | registration_day        |
+----------+---------+--------+-------------------+-------------------------+
|    101   |  Alex   |   25   | 2022-09-12        | 2                       |
|    102   |  Emily  |   15   | 2022-01-12        | 4                       |
|    103   |  Jean   |   35   | 2022-01-22        | 7                       |
|    104   |  Bob    |   40   | 2022-02-01        | 3                       |
+----------+---------+--------+-------------------+-------------------------+

Tip: this function is useful for example to get statistics of the number of registered users depending on the day of the week. Such statistics will be possible using GROUP BY command.
 
 

Example 3: filter only Mondays:

It is possible to filter the results to get only the users registered on Monday by using DAYOFWEEK() function in the search with WHERE, as follows:

SELECT *
FROM users
WHERE DAYOFWEEK(registration_date) = 2

Output:

+----------+---------+--------+-------------------+-------------------------------+
|  UserID  |  Name   |  age   | registration_date |             Address           |
+----------+---------+--------+-------------------+-------------------------------+
|    101   |  Alex   |   25   | 2022-09-12        | 819 Saint Francis Way         |
+----------+---------+--------+-------------------+-------------------------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More