MySQL Day Functions are used to manipulate dates to get day part in a specific way. The possible options are day number of a week, month or year.
In the following functions datetime value is needed. You can use table column names having datetime data type. If you want to use day value of current date/ system date you can use the current system date by using current date functions as discussed under MySQL Date Functions.
Here is a list of most commonly used MySQL Day Functions.
DAY() and DAYOFMONTH()
Both these MySQL day functions are used to do the same task- getting the day of the month as number. The value is between 1-31 representing day of the month. Syntax of the function is-
DAY(date_value)
DAYOFMONTH(date_value)
Both functions need a date as argument.
Example
SELECT CURDATE(),DAY(CURDATE()); SELECT CURDATE(), DAYOFMONTH(CURDATE());
DAYNAME()
This function returns the name of the day of the week. The output is a string value for the name of the weekday one from Sunday to Saturday. Its Syntax is
DAYNAME (date_value)
This function needs a date as argument.
Example
SELECT CURDATE(), DAYNAME(CURDATE());
DAYOFWEEK()
This function returns the day number of the week. The output is an integer value for the weekday between 1 and 7. 1 is for Sunday and 7 for Saturday. Its Syntax is
DAYOFWEEK (date_value)
This function needs a date as argument.
Example
SELECT CURDATE(), DAYOFWEEK(CURDATE());
DAYOFYEAR()
This function returns the day number of the year. The output is an integer value for the year day between 1 and 365. Its Syntax is
DAYOFWEEK (date_value)
This function needs a date as argument.
Example
SELECT CURDATE(), DAYOFYEAR(CURDATE());
LAST_DAY()
This function returns last day of the month of a date. The output is a date value representing the last day of that month. Its Syntax is
LAST_DAY (date_value)
This function needs a date as argument.
Example
SELECT CURDATE(), LAST_DAY(CURDATE());
WEEKDAY()
This function returns the index number of the weekday of a given date. The output is an index value between 0 and 6. 0 represents Monday, 1 represents Tuesday and so on. Its Syntax is
WEEKDAY (date_value)
This function needs a date as argument.
Example
SELECT CURDATE(), WEEKDAY(CURDATE());
