WEEK

MySQL

WEEK(date[,mode])

• date: This parameter indicates the date value from which the week is to be extracted. It should be in a format accepted by MySQL, such as ‘YYYY-MM-DD’.
• mode: This optional parameter determines how the function calculates the week number. For example, values range from 0 to 7, where each value specifies a different method of counting. If not provided, the default value is 0, which means the week starts on Sunday and week 1 is the first week with a Sunday in the current year.

Example

SELECT WEEK('2022-03-01');

Output

9

Explanation

The WEEK() function in MySQL returns the week number for a given date. In the given example, ‘2022-03-01’ corresponds to the 9th week of the year.

SQL Server

WEEK( date )

• date: The date parameter determines the corresponding week number for the specified date. This should be an Expression of the date or datetime datatype, such as ‘yyyy-mm-dd’ or ‘yyyy-mm-dd hh

Example

SELECT DATEPART(WEEK, '2022-02-28') AS WeekNumber;

Output

WeekNumber
9

Explanation

The DATEPART function in SQL Server extracts the parts of a given date. In this example, it returns the week number from the given date ‘2022-02-28’. As this date falls into the 9th week of the year 2022, the output is 9.

Oracle

WEEK(date)

• date: The parameter date refers to the date value from which the week number is to be extracted. This is generally provided as a date string or a column that contains date data.

Example

SELECT TO_CHAR(TO_DATE('22/7/2021', 'dd/mm/yyyy'), 'WW') AS Week_Number
FROM dual;

Output

WEEK_NUMBER
-----------
30

Explanation

The code snippet uses Oracle’s TO_CHAR() function to convert the date ‘22/7/2021’ into the week number ‘30’. The ‘WW’ argument specifies that the output should be in week number format. The dual table is a dummy table in Oracle used for computations that need a table, but don’t actually deal with table data.