WEEKDAY() Function in MySQL

WEEKDAY() function in MySQL is used to find the weekday value for a given date. If the date is NULL, the WEEKDAY() function will return NULL. Otherwise, it returns index for a date i.e., 0 for Monday, 1 for Tuesday, … 6 for Sunday.

Syntax :

WEEKDAY(date)

Parameter : This method accepts one parameter as mentioned above and described below :

• date : The date or datetime from which we want to extract the weekday value.

Returns : It returns the weekday value for a given date.

Example-1 : Finding the weekday value of current date Using WEEKDAY() Function on 27/09/2020.

SELECT WEEKDAY(NOW()) AS  WeekDay;

Output :

mysql> SELECT WEEKDAY(NOW()) AS WeekDay;
+---------+
| WeekDay |
+---------+
|       6 |
+---------+

So, the current day is ‘Sunday’ as WEEKDAY Function returns 6.

Example-2 : Finding the weekday value from given datetime Using WEEKDAY () Function.

SELECT WEEKDAY ('2017-08-22 08:09:22') AS WeekDay_Value ;

Output :

+---------------+
| WeekDay_Value |
+---------------+
|             1 |
+---------------+

So, the day is ‘Tuesday’ in this example.

Example-3 : Finding the weekday value from given datetime using WEEKDAY () Function when the date is NULL.

SELECT WEEKDAY (NULL) AS WeekDay_Value ;

Output :

+---------------+
| WeekDay_Value |
+---------------+
|          NULL |
+---------------+

Example-4 : In this example we are going to find number of Product sold for every weekday. To demonstrate create a table named.

Product :

CREATE TABLE Product(
    Product_id INT AUTO_INCREMENT, 
    Product_name VARCHAR(100) NOT NULL,
    Buying_price DECIMAL(13, 2) NOT NULL,
    Selling_price DECIMAL(13, 2) NOT NULL,
    Selling_Date Date NOT NULL,
    PRIMARY KEY(Product_id)
);

Now inserting some data to the Product table :

INSERT INTO 
    Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
    ('Audi Q8', 10000000.00, 15000000.00, '2020-08-26' ),
    ('Volvo XC40', 2000000.00, 3000000.00, '2020-08-27' ),
    ('Audi A6', 4000000.00, 5000000.00, '2020-08-28' ),
    ('BMW X5', 5000500.00, 7006500.00, '2020-09-01'  ),
    ('Jaguar XF', 5000000, 7507000.00, '2020-09-04'  ),
        ('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2020-09-05'  ),
        ('Jaguar F-PACE', 5000000.00, 7000000.00, '2020-09-08'  ),
        ('Volvo S90', 4500000.00, 6000000.00, '2020-09-11'  ),
        ('BMW X4', 4000000.00, 6200000.00, '2020-09-12'  ),
    ('Porsche Macan', 6500000.00, 8000000.00, '2020-09-16' ) ;

So, the Product table is :

mysql> Select * from Product;
+------------+-----------------------+--------------+---------------+--------------+
| Product_id | Product_name          | Buying_price | Selling_price | Selling_Date |
+------------+-----------------------+--------------+---------------+--------------+
|          1 | Audi Q8               |  10000000.00 |   15000000.00 | 2020-08-26   |
|          2 | Volvo XC40            |   2000000.00 |    3000000.00 | 2020-08-27   |
|          3 | Audi A6               |   4000000.00 |    5000000.00 | 2020-08-28   |
|          4 | BMW X5                |   5000500.00 |    7006500.00 | 2020-09-01   |
|          5 | Jaguar XF             |   5000000.00 |    7507000.00 | 2020-09-04   |
|          6 | Mercedes-Benz C-Class |   4000000.00 |    6000000.00 | 2020-09-05   |
|          7 | Jaguar F-PACE         |   5000000.00 |    7000000.00 | 2020-09-08   |
|          8 | Volvo S90             |   4500000.00 |    6000000.00 | 2020-09-11   |
|          9 | BMW X4                |   4000000.00 |    6200000.00 | 2020-09-12   |
|         10 | Porsche Macan         |   6500000.00 |    8000000.00 | 2020-09-16   |
+------------+-----------------------+--------------+---------------+--------------+

Now, we are going to find number of Product sold for every weekday.

SELECT 
    WEEKDAY (Selling_Date) WeekDay, 
    COUNT(Product_id) Product_Sold
FROM 
    Product
GROUP BY WEEKDAY(Selling_Date)
ORDER BY WEEKDAY(Selling_Date);

Output :

+---------+--------------+
| WeekDay | Product_Sold |
+---------+--------------+
|       1 |            2 |
|       2 |            2 |
|       3 |            1 |
|       4 |            3 |
|       5 |            2 |
+---------+--------------+

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql