The WEEK SQL Function in MySQL and MariaDB - Week index of date |
|
| | WEEK | Syntax: | WEEK(Date [, Mode]) | | Return value: | INTEGER | | Function type: | Date and Time function | |
| | The WEEK() function returns the week index of "Date" in the range from 0 to 53 or from 1 to 53, depending on the specification of "Mode".
The "Date" argument must be of type DATE or DATETIME.
The "Mode" argument can be used to specify whether the week starts on Sunday or Monday and whether the return value should be in the range 0 to 53 or 1 to 53.
If the "Mode" argument is omitted, the value of the default_week_format system variable is used.
If the value for "Date" is invalid or NULL, the WEEK() function returns NULL.
Valid specifications for mode are:
| 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 |
| SQL Examples for the WEEK function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
| SELECT week(now());
SELECT week(adddate(now(), interval 1 day));
SELECT week(utc_timestamp());
SELECT week('2023-03-29');
SELECT week('2023-03-29 12:15:30');
SELECT week(20230216);
SELECT week(20230216121530);
SELECT week('2022-01-02', 0);
SELECT week('2022-01-02', 1);
SELECT week('2022-01-02', 2);
SELECT week('2022-01-02', 3);
SELECT week('2022-01-02', 4);
SELECT week('2022-01-02', 5);
SELECT week('2022-01-02', 6);
SELECT week('2022-01-02', 7);
SELECT week('2023-00-00');
SELECT week(unix_timestamp());
SELECT week('xxx');
SELECT week(null);
show VARIABLES LIKE 'default_week_format';
|
| |
|
| week(adddate(now(), interval 1 day)) |
|---|
| int(2) |
|---|
| 14 |
|
|
| week(utc_timestamp()) |
|---|
| int(2) |
|---|
| 14 |
|
|
| week('2023-03-29') |
|---|
| int(2) |
|---|
| 13 |
|
|
| week('2023-03-29 12:15:30') |
|---|
| int(2) |
|---|
| 13 |
|
| |
|
| week(20230216121530) |
|---|
| int(2) |
|---|
| 7 |
|
|
| week('2022-01-02', 0) |
|---|
| int(2) |
|---|
| 1 |
|
|
| week('2022-01-02', 1) |
|---|
| int(2) |
|---|
| 0 |
|
|
| week('2022-01-02', 2) |
|---|
| int(2) |
|---|
| 1 |
|
|
| week('2022-01-02', 3) |
|---|
| int(2) |
|---|
| 52 |
|
|
| week('2022-01-02', 4) |
|---|
| int(2) |
|---|
| 1 |
|
|
| week('2022-01-02', 5) |
|---|
| int(2) |
|---|
| 0 |
|
|
| week('2022-01-02', 6) |
|---|
| int(2) |
|---|
| 1 |
|
|
| week('2022-01-02', 7) |
|---|
| int(2) |
|---|
| 52 |
|
|
| week('2023-00-00') |
|---|
| int(2) |
|---|
| NULL |
|
|
| week(unix_timestamp()) |
|---|
| int(2) |
|---|
| NULL |
|
| |
| |
|
| Variable_name |  |
|
| Value | |
|
|---|
information_schema.SESSION_VARIABLES
VARIABLE_NAME
varchar(64) UTF8 | information_schema.SESSION_VARIABLES
VARIABLE_VALUE
varchar(2048) UTF8 |
|---|
| default_week_format | 0 |
|
|
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the WEEK() function in MySQL and MariaDB databases | In MySQL and MariaDB the WEEK() function is used to get the week number for a specific "Date" or "Date and Time" expression. The function returns an integer value that represents the week of the year, with the smallest value representing the week containing the first day of the year and the largest value representing the week containing the last day of the year. The mode that determines how the week number is calculated can be specified as an optional parameter. The result may vary depending on the specified mode. The default mode is 0.
Table column names or other expressions can be used as parameters to get the week number in SQL queries, to work with specific weeks or to perform various operations based on the week number, for example to sort, sum or group query results by week number. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | FROM_UNIXTIME | FROM_UNIXTIME(Unix timestamp [, Format]) | | More about FROM_UNIXTIME Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | STR_TO_DATE | STR_TO_DATE(String, Format) | | More about STR_TO_DATE Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMEDIFF | TIMEDIFF(Date 1, Date 2) | | More about TIMEDIFF Function |
| | TIMESTAMP | TIMESTAMP(Date [, Addition]) | | More about TIMESTAMP Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | EXTRACT | EXTRACT(Unit FROM Date) | | More about EXTRACT Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
| | TIME_FORMAT | TIME_FORMAT(Time, Format) | | More about TIME_FORMAT Function |
|
|
| | | More information about the WEEK SQL function: and and |
|
|
|
|
