SQL Reference - YEARWEEK

YEARWEEK(date[, mode])

date: This is a required parameter that determines the input, which should be a date or datetime value from which the function extracts the year and week. It can also accept a string that can be interpreted as a date.
mode: This is an optional parameter that allows control over the way the year and week values are calculated. If omitted, it defaults to 0. This parameter can accept values from 0 to 7, where each number corresponds to a specific behaviour in terms of how the week, year start and end date is calculated and how the week numbers are assigned within the year. For example, when mode is 1, it ensures the week starts on Monday and week 1 is returned for the first week that contains 4 or more days. For other mode values, the behaviour varies similarly.

Example

SELECT YEARWEEK('2022-03-29');

Output

Explanation

The YEARWEEK function in the example returns the year and week number for the date ‘2022-03-29’. The output 202213 indicates the year 2022 and the 13th week of that year.

YEARWEEK(date, mode)Where:- date represents the date value that you wish to evaluate.- mode is an optional parameter that determines how the function will calculate the week and year numbers.

date: This represents the date value that needs to be evaluated. The function uses this parameter to obtain the year and week number.
mode: An optional parameter that governs how the function calculates the year and week numbers. Depending on the method you choose, the start of the week and the return value of the year may vary. If no mode is specified, the default is 0.

Example

SELECT YEARWEEK('2022-01-01');

Output

Explanation

The YEARWEEK function in SQL returns the year and the week number for a given date. In the example, for the date ‘2022-01-01’, it returns ‘202253’ which indicates the 53rd week of the year 2022.