How WEEK() Works in MariaDB

In MariaDB, WEEK() is a built-in date and time function that returns the week from a given date expression.

It accepts two arguments; the date you want to extract the 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.

Syntax

The syntax goes like this:

WEEK(date[,mode])

Where date is the date expression to get the 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 WEEK() without specifying a mode, it will use mode 0.

Example

Here’s an example of calling WEEK() without specifying the mode:

SELECT WEEK('2030-01-01');

Result:

+--------------------+
| WEEK('2030-01-01') |
+--------------------+
|                  0 |
+--------------------+

Specify a Mode

Here’s an example of specifying the mode:

SELECT WEEK('2030-01-01', 1);

Result:

+-----------------------+
| WEEK('2030-01-01', 1) |
+-----------------------+
|                     1 |
+-----------------------+

This time, the result is 1 instead of 0.

Let’s run through all the modes for the same date:

SELECT 
    WEEK('2030-01-01', 0),
    WEEK('2030-01-01', 1),
    WEEK('2030-01-01', 2),
    WEEK('2030-01-01', 3),
    WEEK('2030-01-01', 4),
    WEEK('2030-01-01', 5),
    WEEK('2030-01-01', 6),
    WEEK('2030-01-01', 7);

Result (using vertical output):

WEEK('2030-01-01', 0): 0
WEEK('2030-01-01', 1): 1
WEEK('2030-01-01', 2): 52
WEEK('2030-01-01', 3): 1
WEEK('2030-01-01', 4): 1
WEEK('2030-01-01', 5): 0
WEEK('2030-01-01', 6): 1
WEEK('2030-01-01', 7): 53

Datetime Values

The WEEK() function also works with datetime values:

SELECT WEEK('2030-08-01 10:30:45');

Result:

+-----------------------------+
| WEEK('2030-08-01 10:30:45') |
+-----------------------------+
|                          30 |
+-----------------------------+

Numeric Dates

It’s also possible to pass dates as a number, as long as it makes sense as a date.

Example

SELECT WEEK(20301125);

Result:

+----------------+
| WEEK(20301125) |
+----------------+
|             47 |
+----------------+

Or even the following (which uses a two-digit year):

SELECT WEEK(301125);

Result:

+--------------+
| WEEK(301125) |
+--------------+
|           47 |
+--------------+

But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:

SELECT WEEK(20301135);

Result:

+----------------+
| WEEK(20301135) |
+----------------+
|           NULL |
+----------------+
1 row in set, 1 warning (0.001 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 
    WEEK('2030/06/25'),
    WEEK('2030,06,25'),
    WEEK('2030:06:25'),
    WEEK('2030;06!25');

Result (using vertical output):

WEEK('2030/06/25'): 25
WEEK('2030,06,25'): 25
WEEK('2030:06:25'): 25
WEEK('2030;06!25'): 25

Current Date

We can pass NOW() as the datetime argument to use the current date:

SELECT 
    NOW(),
    WEEK(NOW());

Result:

+---------------------+-------------+
| NOW()               | WEEK(NOW()) |
+---------------------+-------------+
| 2021-05-17 08:36:12 |          20 |
+---------------------+-------------+

Invalid Arguments

When passed an invalid argument, WEEK() returns null:

SELECT WEEK('2030-65-78');

Result:

+--------------------+
| WEEK('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 WEEK() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT WEEK();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And another example:

SELECT WEEK('2030-12-10', 1, 2);

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' 2)' at line 1