Updated May 26, 2023
Introduction to MySQL WEEKDAY
WEEKDAY function in MySQL returns the day index corresponding to a date. This function accepts only one argument at a time in either a date format or date_time format. Using the CURDATE function to specify the date as an argument in the WEEKDAY function is a valid case. The function assigns 0 to Monday, 1 to Tuesday, 2 to Wednesday, … and 6 to Sunday. So all seven days a week have a digit attached to it which will be returned as the output. The function returns a valid index for a valid date but a ‘NULL’ value for an invalid date, a 0 value, and blank parameters.
Let’s see the syntax and examples of this function.
Syntax
The syntax for the WEEKDAY function is as below:
WEEKDAY (date);
The input parameter for the function is the calendar date for which the index is to be retrieved.
We can see examples of the usage below:
How does the FORMAT function work in MySQL?
The WEEKDAY function returns an INT output, a digit index of the week corresponding to the calendar date. We can see different possible examples below.
Code:
SELECT WEEKDAY ('2020-01-01'), DAYNAME ('2020-01-01');
This query is to return the day index and day name of January 01, 2020.
Output:
So we can see that the output for the WEEKDAY function is two because it is the weekday index corresponding to January 01, 2020, which is a Wednesday.
Code:
We will write a query that returns the weekday index of a date when the argument is provided in the date_time format.
SELECT WEEKDAY('2020-01-10 20:00:00');
Output:
January 10, 2020, is a Friday; thus the output of the query is 4.
Code:
We saw valid scenarios in which we retrieved a good day index for the function. Let’s see an invalid date and corresponding output.
SELECT WEEKDAY('2020-02-30');
Output:
The month of February does not have day 30. So this is an invalid date. So the output is ‘NULL.’
Code:
The expected output is ‘NULL’ if no argument is passed in the function.
SELECT WEEKDAY('');
Output:
Code:
Like an invalid date, a 0 date is also expected to return NULL as the output.
SELECT WEEKDAY('0000-00-00');
Output:
The date argument input ‘0000-00-00’ is an invalid date, and thus it also returns NULL as output.
SCENARIO
We already discussed that the WEEKDAY function picks only one argument in a line of query. If multiple dates are to be input, then a separate WEEKDAY function call must be made. Executing the WEEKDAY function with multiple date inputs within the same parenthesis will lead to an error output.
Code:
SELECT WEEKDAY('2020-02-03','202020-05-10');
Output:
We have received an error message as the query is an error. The response is “Error Code: 1582. Incorrect parameter count in the call to native function ‘WEEKDAY'”. And the response says the parameter count in the call is incorrect.
Code:
Let’s write a query to retrieve all seven days a week in order.
SELECT WEEKDAY('2020-02-03') Feb_03,
WEEKDAY('2020-02-04') Feb_04,
WEEKDAY('2020-02-05') Feb_05,
WEEKDAY('2020-02-06') Feb_06,
WEEKDAY('2020-02-07') Feb_07,
WEEKDAY('2020-02-08') Feb_08,
WEEKDAY('2020-02-09') Feb_09;
We have used the WEEKDAY function seven times to get valid output.
Output:
When we examine the calendar, we can identify that February 03, 2020, falls on a Monday with an index value of 0. February 04 is a Tuesday with an index value of 1, February 05 is a Wednesday with an index value of 2, February 06 is a Thursday with an index value of 3, February 07 is a Friday with an index value of 4, February 08 is a Saturday with an index value of 5, and finally, February 09 is a Sunday with an index value of 6.
Code:
We can also try the CURDATE function within the WEEKDAY function to get the weekday index of the current date.
SELECT CURDATE() Today, DAYNAME (CURDATE()) Today_dayname, WEEKDAY(CURDATE()) Today_weekday;
Query calls the CURDATE function to retrieve the current date. Also, the CURDATE function is passed as an input argument in the DAYNAME function to retrieve the day name of the current date and in the WEEKDAY function to retrieve the day index of the current date.
Output:
If we study our output closely, we can understand that June 19, 2020, is returned as Today. The corresponding day name for June 19 is Friday; thus, the weekday function returns the output as 4.
Conclusion – MySQL WEEKDAY
The WEEKDAY function retrieves the index of days in a week and always outputs an INT value. The valid input formats are CURDATE() function, DATE format, and DATE_TIME format. The function allows only one argument per function call. Invalid date, zero date, and blank values return ‘NULL’ as output in this function. The function assigns an index of 0 to Monday, 1 to Tuesday, and thus 6 to Sunday.
Recommended Articles
We hope that this EDUCBA information on “MySQL WEEKDAY” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
