MySQL WEEK() Function

 The WEEK() function in MySQL is used to extract the week number from a given date or datetime value. It returns an integer that represents the week number for a specific date. By default, the week starts on Sunday, and the range for the week number is from 0 to 53, depending on the year.

Syntax:

WEEK(date, mode)

• date: The date or datetime value from which the week number is extracted.
• mode (optional): A number that specifies the mode used to determine the start of the week. If omitted, MySQL uses the default mode (mode 0).

Modes for WEEK() function:

The mode determines which day is considered the start of the week, and how weeks are numbered. Here are the common values for mode:

1. 0: Week starts on Sunday (default). Weeks are numbered from 0 to 53.
2. 1: Week starts on Monday. Weeks are numbered from 1 to 53.
3. 2: Week starts on Sunday, and the week containing January 1st is considered week 1.
4. 3: Week starts on Monday, and the week containing January 1st is considered week 1.
5. 4: Week starts on Sunday, and the week containing the first Thursday is considered week 1.
6. 5: Week starts on Monday, and the week containing the first Thursday is considered week 1.
7. 6: Week starts on Saturday. Weeks are numbered from 1 to 53.
8. 7: Week starts on Sunday, and the week containing the first Sunday of the year is week 1.

Example Usage:

1. Basic Example:

   SELECT WEEK('2024-12-23');
   

   This will return the week number for December 23, 2024, assuming the default mode is used.

2. Using a Different Mode:

   SELECT WEEK('2024-12-23', 1); -- Week starts on Monday
   

   This will return the week number where the week starts on Monday.

3. Getting Week Number for Current Date:

   SELECT WEEK(CURDATE());
   

   This will return the current week number for today's date.

Let me know if you'd like more details or examples!