MySQL: DAYOFWEEK() Function

MySQL: DAYOFWEEK() Function; In this tutorial, we will learn how to use mysql DAYOFWEEK() function with the help of examples.

When we need to get day of week of given date in mysql query, at that time we could use to mysql DAYOFWEEK () function. And it will return numeric value.

As well as, We will take some example of with mysql various function like, CURDATE(), NOW() etc.

MySQL DAYOFWEEK() Function

In MySQL, the DAYOFWEEK() is used to return the day of the week from a date.

Syntax

The DAYOFWEEK() function syntax is:

DAYOFWEEK(date)

The date here is the date value that you want the day of the week from which you returned.

Example-1

Now we take an example to demonstrate.

SELECT DAYOFWEEK('2020-06-18') AS 'Result';

Output-1

+--------+
| Result |
+--------+
|     5  |
+--------+

Example-2

If there is a leading zero in the part of the day, then the leading zero has been left out of the result.

SELECT DAYOFWEEK('2019-03-15') AS 'Result';

Output-2

+--------+
| Result |
+--------+
|      6 |
+--------+

Example-3 | Database Example

Next database example, we take an example for demostration with database.

 SELECT
 created_at AS create_date,
 DAYNAME(created_at) AS 'Day Name', 
 DAYOFWEEK(created_at) AS day_of_week
 FROM users
 WHERE id= 112;

Output-3

+---------------------+-----------+-------------+
|  create_date        | Day Name  | Day of Week |
+---------------------+-----------+-------------+
| 2005-05-25 11:30:37 | Wednesday |           4 |
+---------------------+-----------+-------------+

Example-4 | Current Date/Time

Let’s take another example , extracting part of the day from the current date and time (which is now returned using the () function).

  SELECT 
  NOW(),
  DAYOFWEEK(NOW());

Output-4

+---------------------+--------------------+
| NOW()               | DAYOFWEEK(NOW())   |
+---------------------+--------------------+
| 2019-07-10 18:30:44 |         6          |
+---------------------+--------------------+

Example-4 | CURDATE() Function

Let’s take a another example of using CURDATE() function. Basically CURDATE() function returns only the date without time.

SELECT 
CURDATE(),
DAYOFWEEK(CURDATE());

Output-5

+------------+-----------------------+
| CURDATE()  | DAYOFWEEK(CURDATE())  |
+------------+-----------------------+
| 2019-05-15 |              6        |
+------------+-----------------------+

Example-6 DAYOFWEEK() v/s DAYOFMONTH()

Now let us take an example to explain the difference between DAYOFWEEK() and DAYOFMONTH() function of MySQL.

SET @date = '2020-02-15';
 SELECT 
     @date AS 'DATE',
     DAYNAME(@date) AS 'Day Name',
     DAYOFWEEK(@date) AS 'Day of Week',
     DAYOFMONTH(@date) AS 'Day of Month';

Output-6

+------------+----------+-------------+--------------+
| @date      | Day Name | Day of Week | Day of Month |
+------------+----------+-------------+--------------+
| 2020-02-15 | Saturday |           7 |           15 |
+------------+----------+-------------+--------------+

Note:

DAYOFMONTH () function returns the value between 1 and 31
On the other hand, the DAYOFWEEK () function returns a value between 1 and 7.

Example-7 of WEEKDAY() vs DAYOFWEEK()

In example-7, we take an example to differentiate weekday() and dayofweek() function.

 SET @date = '2019-07-13';
 SELECT 
   DAYOFWEEK(@date) AS 'Day OF WEEK',
   WEEKDAY(@date) AS 'Weekday';

WEEKDAY()	DayofWeek()
MySQL WEEKDAY() function returns the weekday number for a given date(between 0 to 6).	MySQL DAYOFWEEK() function returns the weekday index for a given date (between 1 to 7).
1 to 7 means: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.	0 to 6 means: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.

Output-7

+----------+-----------------+
| Day Of Name | Weekday      |
+-------------+--------------+
| 7           |       5      |
+-------------+--------------+

Conclusion

Here, You have learned how to use mysql DAYOFWEEK() function with various examples.