The YEARWEEK SQL Function in MySQL and MariaDB - Year and week of date |
|
| | YEARWEEK | Syntax: | YEARWEEK(Date [, Mode]) | | Return value: | INTEGER | | Function type: | Date and Time function | |
| | The YEARWEEK() function returns the year and week of "Date".
The "Date" argument must be of type DATE or DATETIME.
The "Mode" argument can be used to specify whether the week of the year 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 YEARWEEK() 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 YEARWEEK 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 yearweek(now());
SELECT yearweek(adddate(now(), interval 1 day));
SELECT yearweek(utc_timestamp());
SELECT yearweek('2023-03-29');
SELECT yearweek('2023-03-29 12:15:30');
SELECT yearweek(20230216);
SELECT yearweek(20230216121530);
SELECT yearweek('2022-01-02', 0);
SELECT yearweek('2022-01-02', 1);
SELECT yearweek('2022-01-02', 2);
SELECT yearweek('2022-01-02', 3);
SELECT yearweek('2022-01-02', 4);
SELECT yearweek('2022-01-02', 5);
SELECT yearweek('2022-01-02', 6);
SELECT yearweek('2022-01-02', 7);
SELECT yearweek('2023-00-00');
SELECT yearweek(unix_timestamp());
SELECT yearweek('xxx');
SELECT yearweek(null);
SHOW VARIABLES LIKE 'default_week_format';
|
|
| yearweek(now()) |
|---|
| int(6) |
|---|
| 202314 |
|
|
| yearweek(adddate(now(), interval 1 day)) |
|---|
| int(6) |
|---|
| 202314 |
|
|
| yearweek(utc_timestamp()) |
|---|
| int(6) |
|---|
| 202314 |
|
|
| yearweek('2023-03-29') |
|---|
| int(6) |
|---|
| 202313 |
|
|
| yearweek('2023-03-29 12:15:30') |
|---|
| int(6) |
|---|
| 202313 |
|
|
| yearweek(20230216) |
|---|
| int(6) |
|---|
| 202307 |
|
|
| yearweek(20230216121530) |
|---|
| int(6) |
|---|
| 202307 |
|
|
| yearweek('2022-01-02', 0) |
|---|
| int(6) |
|---|
| 202201 |
|
|
| yearweek('2022-01-02', 1) |
|---|
| int(6) |
|---|
| 202152 |
|
|
| yearweek('2022-01-02', 2) |
|---|
| int(6) |
|---|
| 202201 |
|
|
| yearweek('2022-01-02', 3) |
|---|
| int(6) |
|---|
| 202152 |
|
|
| yearweek('2022-01-02', 4) |
|---|
| int(6) |
|---|
| 202201 |
|
|
| yearweek('2022-01-02', 5) |
|---|
| int(6) |
|---|
| 202152 |
|
|
| yearweek('2022-01-02', 6) |
|---|
| int(6) |
|---|
| 202201 |
|
|
| yearweek('2022-01-02', 7) |
|---|
| int(6) |
|---|
| 202152 |
|
|
| yearweek('2023-00-00') |
|---|
| int(6) |
|---|
| NULL |
|
|
| yearweek(unix_timestamp()) |
|---|
| int(6) |
|---|
| NULL |
|
|
| yearweek('xxx') |
|---|
| int(6) |
|---|
| NULL |
|
|
| yearweek(null) |
|---|
| int(6) |
|---|
| 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 YEARWEEK() function in MySQL and MariaDB databases | In MySQL and MariaDB the YEARWEEK() function can be used to get the year and week number for a specific "Date" or "Date and Time" expression. The function returns an integer value containing the year and week combined in the format "YYYYWW", where in the week portion the smallest value is the week containing the first day of the year and the largest value is the week containing the last day of the year. As an optional parameter, the mode can be specified, which determines how the weekly portion is calculated. This 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 combination of year and week number in SQL queries, work with specific year-week combinations, or perform various operations based on the year-week combination, for example to sort, sum or group query results by those values. | | 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 |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR 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 YEARWEEK SQL function: and and |
|
|
|
|
