How WEEKOFYEAR() Works in MariaDB

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'