You can use the DAYOFWEEK() function in MySQL to return the day of the week from a date.

In this context, a return value of 1 corresponds to Sunday, 2 corresponds to Monday, etc.

This article contains examples to demonstrate.

Syntax

The syntax goes like this:

DAYOFWEEK(date)

Where date is the date value that you want the day of the week returned from.

Example

Here’s an example to demonstrate.

SELECT DAYOFWEEK('2019-01-01') AS 'Result';

Result:

+--------+
| Result |
+--------+
|      3 |
+--------+

We can run this alongside the DAYNAME() function to see which day it corresponds to:

SET @date = '2019-01-01';
SELECT 
    @date,
    DAYNAME(@date) AS 'Day Name',
    DAYOFWEEK(@date) AS 'Day of Week';

Result:

+------------+----------+-------------+
| @date      | Day Name | Day of Week |
+------------+----------+-------------+
| 2019-01-01 | Tuesday  |           3 |
+------------+----------+-------------+

DAYOFWEEK() vs DAYOFMONTH()

Here’s an example to demonstrate the difference between the DAYOFWEEK() and DAYOFMONTH() functions.

SET @date = '2019-01-20';
SELECT 
    @date,
    DAYNAME(@date) AS 'Day Name',
    DAYOFWEEK(@date) AS 'Day of Week',
    DAYOFMONTH(@date) AS 'Day of Month';

Result:

+------------+----------+-------------+--------------+
| @date      | Day Name | Day of Week | Day of Month |
+------------+----------+-------------+--------------+
| 2019-01-20 | Sunday   |           1 |           20 |
+------------+----------+-------------+--------------+

The DAYOFMONTH() function returns a value between 1 and 31 (or 0 for dates with a zero day part) that represents the day of the month. It resets back to 1 at the start of each month.

The DAYOFWEEK() function on the other hand, returns a value between 1 and 7. It resets back to 1 at the start of each week.

A Database Example

Here, we do a similar thing to the previous examples, except that this example uses data from a database:

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DAYNAME(payment_date) AS 'Day Name',
  DAYOFWEEK(payment_date) AS 'Day of Week'
FROM payment
WHERE payment_id = 1;

Result:

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

Current Date/Time

Here’s an example of extracting the day part from the current date and time (which is returned using the NOW() function).

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

Result:

+---------------------+------------------+
| NOW()               | DAYOFWEEK(NOW()) |
+---------------------+------------------+
| 2018-06-26 07:42:31 |                3 |
+---------------------+------------------+

Another way to do this is to use the CURDATE() function, which returns only the date (but not the time).

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

Result:

+------------+----------------------+
| CURDATE()  | DAYOFWEEK(CURDATE()) |
+------------+----------------------+
| 2018-06-26 |                    3 |
+------------+----------------------+