In MySQL Date Or Time Function Part-8 we will discuss about the TO_DAYS, WEEK, WEEKDAY, WEEKOFYEAR, YEAR, YEARWEEK
TO_DAYS Function:- TO_DAYS Function is used for returning the date between a date and year from 0. It uses the Gregorian calendar dates.
Syntax:- TO_DAYS(date)
Example:-
SELECT TO_DAYS(“2023-02-15”);
Output:- 738931
WEEK Function:- WEEK Function is used for getting a number of weeks from a given date. The number of weeks ranges will be between 0 to 53.
Syntax:- WEEK(date, mode)
The mode can specify the following way:-
Mode 0, Sunday FIRST DAY OF WEEK with a Sunday in this year, Range 0-53
Mode 1, Monday FIRST DAY OF WEEKwith 4 or more days this year, Range 0-53
Mode 2, Sunday FIRST DAY OF WEEK with a Sunday in this year’s Range, 1-53
Mode 3, Monday FIRST DAY OF WEEK with 4 or more days this year, Range 1-53
Mode 4, Sunday FIRST DAY OF WEEK with 4 or more days this year, Range 0-53
Mode 5, Monday FIRST DAY OF WEEK with a Monday in this year, Range 0-53
Mode 6, Sunday FIRST DAY OF WEEK with 4 or more days this year, Range 1-53
Mode 7, Monday FIRST DAY OF WEEK with a Monday in this year, Range 1-53
Example:-
SELECT WEEK(“2023-02-15”);
Output:- 7
SELECT WEEK(NOW());
WEEKDAY Function:- WEEKDAY Function is used for returning the numeric value of a weekday from a given date.
The numeric value of the week will be:-
0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.
Syntax:- WEEKDAY(date)
Example:-
SELECT WEEKDAY(“2023-02-15”);
Output:- 2
WEEKOFYEAR Function:- WEEKOFYEAR Function is used for returning a number of weeks from a given date. This function considers the first day of the week as Monday and the first week of the year is more than 3.
Syntax:- WEEKOFYEAR(date)
Example:- SELECT WEEKOFYEAR(“2023-02-15”);
Output:- 7
YEAR Function:- YEAR Function is used for returning the year part from a given date.
Syntax:- YEAR(date)
Example:-
SELECT YEAR(“2023-02-15”);
Output:- 2023
YEARWEEK Function:- YEARWEEK Function is used for returning a number of weeks and years from a given date. The number range will be 0 to 53.
Syntax:- YEARWEEK(date, first day of the week)
The first day of the week is the optional part we can use 0 for Sunday,1 for Monday(if a week has more than 3 days), 2 For Sunday, 3 for Monday(if a week has more than 3 days), 4 For Sunday and (if a week has more than 3 days), 5 For Monday, 6 For Sunday and (if a week has more than 3 days). 7 For Monday
Example:-
SELECT YEARWEEK(“2023-02-15”);
Output:-202307
SELECT YEARWEEK(CURDATE());
Read Also:-
MySQL Date Or Time Function Part-1
MySQL Date Or Time Function Part-2
MySQL Date Or Time Function Part-3
MySQL Date Or Time Function Part-4
MySQL Date Or Time Function Part-5
MySQL Date Or Time Function Part-6
MySQL Date Or Time Function Part-7Read more detail about MySQL Date Or Time Function manual