WEEK(date[,mode])
This function returns the week number for date
. The two-argument form of WEEK()
allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode
argument is omitted, the value of the default_week_format system variable is used.
Mode | 1st day of week | Range | Week 1 is the 1st week with |
---|---|---|---|
0 | Sunday | 0-53 | a Sunday in this year |
1 | Monday | 0-53 | more than 3 days this year |
2 | Sunday | 1-53 | a Sunday in this year |
3 | Monday | 1-53 | more than 3 days this year |
4 | Sunday | 0-53 | more than 3 days this year |
5 | Monday | 0-53 | a Monday in this year |
6 | Sunday | 1-53 | more than 3 days this year |
7 | Monday | 1-53 | a Monday in this year |
SELECT WEEK('2008-02-20'); +--------------------+ | WEEK('2008-02-20') | +--------------------+ | 7 | +--------------------+ SELECT WEEK('2008-02-20',0); +----------------------+ | WEEK('2008-02-20',0) | +----------------------+ | 7 | +----------------------+ SELECT WEEK('2008-02-20',1); +----------------------+ | WEEK('2008-02-20',1) | +----------------------+ | 8 | +----------------------+ SELECT WEEK('2008-12-31',0); +----------------------+ | WEEK('2008-12-31',0) | +----------------------+ | 52 | +----------------------+ SELECT WEEK('2008-12-31',1); +----------------------+ | WEEK('2008-12-31',1) | +----------------------+ | 53 | +----------------------+
CREATE TABLE t1 (d DATETIME); INSERT INTO t1 VALUES ("2007-01-30 21:31:07"), ("1983-10-15 06:42:51"), ("2011-04-21 12:34:56"), ("2011-10-30 06:31:41"), ("2011-01-30 14:03:25"), ("2004-10-07 11:19:34");
SELECT d, WEEK(d,0), WEEK(d,1) from t1; +---------------------+-----------+-----------+ | d | WEEK(d,0) | WEEK(d,1) | +---------------------+-----------+-----------+ | 2007-01-30 21:31:07 | 4 | 5 | | 1983-10-15 06:42:51 | 41 | 41 | | 2011-04-21 12:34:56 | 16 | 16 | | 2011-10-30 06:31:41 | 44 | 43 | | 2011-01-30 14:03:25 | 5 | 4 | | 2004-10-07 11:19:34 | 40 | 41 | +---------------------+-----------+-----------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/week/