WEEK( )

This MySQL function returns the number of the week starting from the beginning of the year for the date provided.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

WEEK(date[, value])

Explanation

This function returns the number of the week starting from the beginning of the year for the date provided. This may seem simple enough. However, it's complex because there is one or two more days in a year beyond 52 weeks (i.e., 52 x 7 = 364); the first day of the year usually isn't the first day of a week. When a year starts on a Sunday, if you consider Sunday to be the first day of the week, January 1 is definitely the first week of the year. In that case, the function should return 0 or 1 depending on whether you think of 0 as the first number or 1. If you consider Monday the first day of the week, though, then if January 1 is a Sunday, the question is then whether you want that day to be considered as part of the last week of the previous year, or just week 0 of this year and make 1 represent the first full week of the current year. All of these possibilities for MySQL to consider when executing WEEK( ) are represented by the mode you specify as its second parameter.

The range of values accepted for the function's second parameter is 0 to 7. Even numbers indicate that Sunday is the first day of the week; an odd value indicates Monday is the first day of the week. Codes 0, 1, 4, and 5 return results ranging from 0 to 53; codes 2, 3, 6, and 7 return results ranging from 1 to 53. Codes 0, 2, 5, and 7 determine results of the date given with regard to the year that holds the first day of the week of the week that the first day of the year given is in.

Examples

SELECT DAYNAME('2006-01-01') AS 'Day', 
WEEK('2006-01-01', 0) AS '0(S,0)', WEEK('2006-01-01', 1) AS '1(M,0)', 
WEEK('2006-01-01', 2) AS '2(S,1)', WEEK('2006-01-01', 3) AS '3(M,1)', 
WEEK('2006-01-01', 4) AS '4(S,0)', WEEK('2006-01-01', 5) AS '5(M,0)', 
WEEK('2006-01-01', 6) AS '6(S,1)', WEEK('2006-01-01', 7) AS '7(M,1)' UNION ...

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+
| Day       | 0(S,0) | 1(M,0) | 2(S,1) | 3(M,1) | 4(S,0) | 5(M,0) | 6(S,1) | 7(M,1) |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+
| Sunday    |      1 |      0 |      1 |     52 |      1 |      0 |      1 |     52 | 
| Monday    |      0 |      1 |     53 |      1 |      1 |      1 |      1 |      1 | 
| Tuesday   |      0 |      1 |     52 |      1 |      1 |      0 |      1 |     53 | 
| Wednesday |      0 |      1 |     52 |      1 |      1 |      0 |      1 |     52 | 
| Thursday  |      0 |      1 |     52 |      1 |      0 |      0 |     53 |     52 | 
| Friday    |      0 |      0 |     52 |     53 |      0 |      0 |     52 |     52 | 
| Saturday  |      0 |      0 |     52 |     52 |      0 |      0 |     52 |     52 | 
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+

This results set was created with the SELECT statement shown repeated six times, joined together with UNION to merge the results into one results table. The year was adjusted for each SELECT statement, ranging from 2006 to 2011, and 2014 used in the middle for the Wednesday due to leap year. This chart shows the results of WEEK( ) for seven different dates (one for each day of the week), all the first day of their respective years. For each date, each row shows the results for each parameter possibility for the WEEK( ) function. The column headings specify the parameter used, along with whether the parameter considers Sunday or Monday (indicated by S or M, respectively) to be the first day of the week. The 0 just after the S or M indicates that results can range from 0 to 53 weeks; 1 indicates a range of 1 to 53. It's a complex chart, but the subject is complex and it's hoped that seeing all of the possibilities will make it easier to understand. The table below may also be useful in choosing the mode that you want.

If no mode is specified with the WEEK( ) function, the default is used. The default value is stored in the system variable default_week_format. It can be changed with the SET statement.

SHOW VARIABLES LIKE 'default_week_format';
 
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     | 
+---------------------+-------+

SET default_week_format = 1;

As an alternative to WEEK( ), you can use YEARWEEK( ). It's synonymous with WEEK( ), but with the mode of 3 only.

  • 0
  • Sunday 0-53 First Day of Week Considered

  • 1
  • Monday 0-53

  • 2
  • Sunday 1-53 First Day of Week Considered

  • 3
  • Monday 1-53

  • 4
  • Sunday 0-53

  • 5
  • Monday 0-53 First Day of Week Considered

  • 6
  • Sunday 1-53

    7

    Monday 1-53 First Day of Week Considered