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 | +---------+--------------+