In MariaDB, YEARWEEK() is a built-in date and time function that returns the year and week for a given date.
It accepts two arguments; the date you want to extract the year and week from, and an optional mode argument to specify the mode to use in the result.
It returns the week as a number in the range 0 to 53 or 1 to 53, depending on the mode being used. Also, the year in the result may be different from the year in the date argument for the first and the last week of the year.
Syntax
The syntax goes like this:
YEARWEEK(date), YEARWEEK(date,mode)Which could also be expressed like this:
YEARWEEK(date[,mode])Where date is the date expression to get the year and week from, and mode is an optional argument that lets you specify a mode to use.
Modes
The optional mode argument determines 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.
The mode argument can be any of the following:
| 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 (in accordance with ISO 8601:1988) |
| 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 |
If the mode argument is omitted, the value of the default_week_format system variable is used.
Let’s check the value of my default_week_format system variable:
SELECT @@default_week_format;Result:
+-----------------------+ | @@default_week_format | +-----------------------+ | 0 | +-----------------------+
Now, when I run YEARWEEK() without specifying a mode, it will use mode 0.
Example
Here’s an example of calling YEARWEEK() without specifying the mode:
SELECT YEARWEEK('2030-01-01');Result:
+------------------------+
| YEARWEEK('2030-01-01') |
+------------------------+
| 202952 |
+------------------------+
In this case the resulting year and week are from the previous year.
Specify a Mode
Here’s an example of specifying the mode:
SELECT YEARWEEK('2030-01-01', 1);Result:
+---------------------------+
| YEARWEEK('2030-01-01', 1) |
+---------------------------+
| 203001 |
+---------------------------+
This time, the result is different.
Let’s run through all the modes for the same date:
SELECT
YEARWEEK('2030-01-01', 0),
YEARWEEK('2030-01-01', 1),
YEARWEEK('2030-01-01', 2),
YEARWEEK('2030-01-01', 3),
YEARWEEK('2030-01-01', 4),
YEARWEEK('2030-01-01', 5),
YEARWEEK('2030-01-01', 6),
YEARWEEK('2030-01-01', 7);Result (using vertical output):
YEARWEEK('2030-01-01', 0): 202952
YEARWEEK('2030-01-01', 1): 203001
YEARWEEK('2030-01-01', 2): 202952
YEARWEEK('2030-01-01', 3): 203001
YEARWEEK('2030-01-01', 4): 203001
YEARWEEK('2030-01-01', 5): 202953
YEARWEEK('2030-01-01', 6): 203001
YEARWEEK('2030-01-01', 7): 202953
Datetime Values
The YEARWEEK() function also works with datetime values:
SELECT YEARWEEK('2030-08-01 10:30:45');Result:
+---------------------------------+
| YEARWEEK('2030-08-01 10:30:45') |
+---------------------------------+
| 203030 |
+---------------------------------+
Numeric Dates
It’s also possible to pass dates as a number, as long as it makes sense as a date.
Example
SELECT YEARWEEK(20301125);Result:
+--------------------+ | YEARWEEK(20301125) | +--------------------+ | 203047 | +--------------------+
Or even the following (which uses a two-digit year):
SELECT YEARWEEK(301125);Result:
+------------------+ | YEARWEEK(301125) | +------------------+ | 203047 | +------------------+
But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:
SELECT YEARWEEK(20301135);Result:
+--------------------+ | YEARWEEK(20301135) | +--------------------+ | NULL | +--------------------+ 1 row in set, 1 warning (0.000 sec)
It returned null with a warning.
Let’s look at the warning:
SHOW WARNINGS;Result:
+---------+------+--------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------+ | Warning | 1292 | Incorrect datetime value: '20301135' | +---------+------+--------------------------------------+
Other Delimiters
You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:
SELECT
YEARWEEK('2030/06/25'),
YEARWEEK('2030,06,25'),
YEARWEEK('2030:06:25'),
YEARWEEK('2030;06!25');Result (using vertical output):
YEARWEEK('2030/06/25'): 203025
YEARWEEK('2030,06,25'): 203025
YEARWEEK('2030:06:25'): 203025
YEARWEEK('2030;06!25'): 203025
Current Date
We can pass NOW() as the datetime argument to use the current date:
SELECT
NOW(),
YEARWEEK(NOW());Result:
+---------------------+-----------------+ | NOW() | YEARWEEK(NOW()) | +---------------------+-----------------+ | 2021-05-17 09:08:23 | 202120 | +---------------------+-----------------+
Invalid Arguments
When passed an invalid argument, YEARWEEK() returns null:
SELECT YEARWEEK('2030-65-78');Result:
+------------------------+
| YEARWEEK('2030-65-78') |
+------------------------+
| NULL |
+------------------------+
1 row in set, 1 warning (0.000 sec)
Let’s show the warning:
SHOW WARNINGS;Result:
+---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2030-65-78' | +---------+------+----------------------------------------+
Missing Argument
Calling YEARWEEK() with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT YEARWEEK();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'YEARWEEK'
And another example:
SELECT YEARWEEK('2030-12-10', 1, 2);Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'YEARWEEK'