In MariaDB, WEEKOFYEAR()
is a built-in date and time function that returns the calendar week of a given date as a number in the range from 1
to 53
.
It’s the equivalent of using the WEEK()
function in mode 3, and therefore its result is in accordance with ISO 8601:1988.
It accepts one argument; the date you want to extract the week from.
Syntax
The syntax goes like this:
WEEKOFYEAR(date)
Where date
is the date expression to get the calendar week from.
Example
Here’s an example:
SELECT WEEKOFYEAR('2030-01-01');
Result:
+--------------------------+ | WEEKOFYEAR('2030-01-01') | +--------------------------+ | 1 | +--------------------------+
It’s the equivalent of doing the following:
SELECT WEEK('2030-01-01', 3);
Result:
+-----------------------+ | WEEK('2030-01-01', 3) | +-----------------------+ | 1 | +-----------------------+
Datetime Values
The WEEKOFYEAR()
function also works with datetime values:
SELECT WEEKOFYEAR('2030-08-01 10:30:45');
Result:
+-----------------------------------+ | WEEKOFYEAR('2030-08-01 10:30:45') | +-----------------------------------+ | 31 | +-----------------------------------+
Numeric Dates
It’s also possible to pass dates as a number, as long as it makes sense as a date.
Example
SELECT WEEKOFYEAR(20301125);
Result:
+----------------------+ | WEEKOFYEAR(20301125) | +----------------------+ | 48 | +----------------------+
Or even the following (which uses a two-digit year):
SELECT WEEKOFYEAR(301125);
Result:
+--------------------+ | WEEKOFYEAR(301125) | +--------------------+ | 48 | +--------------------+
But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:
SELECT WEEKOFYEAR(20301135);
Result:
+----------------------+ | WEEKOFYEAR(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
WEEKOFYEAR('2030/06/25'),
WEEKOFYEAR('2030,06,25'),
WEEKOFYEAR('2030:06:25'),
WEEKOFYEAR('2030;06!25');
Result (using vertical output):
WEEKOFYEAR('2030/06/25'): 26 WEEKOFYEAR('2030,06,25'): 26 WEEKOFYEAR('2030:06:25'): 26 WEEKOFYEAR('2030;06!25'): 26
Current Date
We can pass NOW()
as the datetime argument to use the current date:
SELECT
NOW(),
WEEKOFYEAR(NOW());
Result:
+---------------------+-------------------+ | NOW() | WEEKOFYEAR(NOW()) | +---------------------+-------------------+ | 2021-05-17 09:42:13 | 20 | +---------------------+-------------------+
Invalid Arguments
When passed an invalid argument, WEEKOFYEAR()
returns null
:
SELECT WEEKOFYEAR('2030-65-78');
Result:
+--------------------------+ | WEEKOFYEAR('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 WEEKOFYEAR()
with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT WEEKOFYEAR();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'WEEKOFYEAR'
And another example:
SELECT WEEKOFYEAR('2030-12-10', 1);
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'WEEKOFYEAR'