WEEK(date), parameter, returns the week of the year in the range 0 to 53: : WEEK « Date Time Functions « MySQL Tutorial

1. MySQL Tutorial
2. Date Time Functions
3. WEEK

mysql>
mysql> SELECT WEEK('2001-12-26');
+--------------------+
| WEEK('2001-12-26') |
+--------------------+
|                 51 |
+--------------------+
1 row in set (0.00 sec)

mysql>

Without a second parameter, WEEK(date) assumes that

1. Sunday is the first day of the week
2. at the beginning of the year
3. any days before the 'first day' come in week 0.

mysql>
mysql> select WEEK('2000-01-01');
+--------------------+
| WEEK('2000-01-01') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql>

You can add the firstday parameter.

0 representing Sunday, 1=Monday, and so on.

mysql>
mysql> select WEEK('2000-01-09');
+--------------------+
| WEEK('2000-01-09') |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.00 sec)

mysql> select WEEK('2000-01-09',1); --you told MySQL to start counting from the Monday
+----------------------+
| WEEK('2000-01-09',1) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)