Below are 5 functions that enable you to return the week number from a date in MariaDB. Four return just the week, and one returns both the week and year combined.

The WEEK() Function

The WEEK() function returns the week for a given date.

Example:

SELECT WEEK('2023-07-25');

Result:

+--------------------+
| WEEK('2023-07-25') |
+--------------------+
|                 30 |
+--------------------+

The WEEK() function accepts an optional second argument to specify the mode. The actual result you get from this function will depend on the mode being used. If the mode argument is omitted, the value of the default_week_format system variable is used.

See How WEEK() Works in MariaDB for more on the modes and examples of each.

The EXTRACT() Function

The EXTRACT() function allows you to extract a specified unit from the date. Therefore, you can use it to extract the week (as well as other units) from the date.

Example:

SELECT EXTRACT(WEEK FROM '2023-07-25');

Result:

+---------------------------------+
| EXTRACT(WEEK FROM '2023-07-25') |
+---------------------------------+
|                              30 |
+---------------------------------+

The DATE_FORMAT() Function

The DATE_FORMAT() function allows you to format a date based on a format string. The format string specifies how the date should be formatted.

You can therefore use this function to return the week (as well as any other unit) from the date. There are various options for returning the week number, depending on which day the week starts on, etc.

Here’s an example that returns the week in various forms:

SELECT 
    DATE_FORMAT('2023-01-01', '%U') AS "%U",
    DATE_FORMAT('2023-01-01', '%u') AS "%u",
    DATE_FORMAT('2023-01-01', '%V') AS "%V",
    DATE_FORMAT('2023-01-01', '%v') AS "%v";

Result:

+------+------+------+------+
| %U   | %u   | %V   | %v   |
+------+------+------+------+
| 01   | 00   | 01   | 52   |
+------+------+------+------+

Here’s a description of each of those format specifiers:

Format Specifier	Description
%U	Week number (00-53), when first day of the week is Sunday.
%u	Week number (00-53), when first day of the week is Monday.
%V	Week number (01-53), when first day of the week is Sunday. Used with %X.
%v	Week number (01-53), when first day of the week is Monday. Used with %x.

And here’s a description of %X and %x as referred to in the above table:

Format Specifier	Description
%X	Year with 4 digits when first day of the week is Sunday. Used with %V.
%x	Year with 4 digits when first day of the week is Monday. Used with %v.

We can combine format specifiers to return the year and week together if required:

SELECT 
    DATE_FORMAT('2023-01-01', '%X, %V') AS "%X, %V",
    DATE_FORMAT('2023-01-01', '%x, %v') AS "%x, %v";

Result:

+----------+----------+
| %X, %V   | %x, %v   |
+----------+----------+
| 2023, 01 | 2022, 52 |
+----------+----------+

Note that the week number won’t always reflect the above examples – it depends on the actual date being used.

Let’s move the date forward a year:

SELECT 
    DATE_FORMAT('2024-01-01', '%U') AS "%U",
    DATE_FORMAT('2024-01-01', '%u') AS "%u",
    DATE_FORMAT('2024-01-01', '%V') AS "%V",
    DATE_FORMAT('2024-01-01', '%v') AS "%v";

Result:

+------+------+------+------+
| %U   | %u   | %V   | %v   |
+------+------+------+------+
| 00   | 01   | 53   | 01   |
+------+------+------+------+

This time we get a different set of results.

Here’s what happens when we move forward into the year:

SELECT 
    DATE_FORMAT('2024-12-12', '%U') AS "%U",
    DATE_FORMAT('2024-12-12', '%u') AS "%u",
    DATE_FORMAT('2024-12-12', '%V') AS "%V",
    DATE_FORMAT('2024-12-12', '%v') AS "%v";

Result:

+------+------+------+------+
| %U   | %u   | %V   | %v   |
+------+------+------+------+
| 49   | 50   | 49   | 50   |
+------+------+------+------+

As you can see, returning the week number isn’t always as clear cut as we might wish for.

See MariaDB Format Strings for a full list of format strings/specifiers that can be used with DATE_FORMAT().

The YEARWEEK() Function

It’s possible to get the week and year in one go. The YEARWEEK() function returns the year and week for a given date.

Example:

SELECT YEARWEEK('2023-01-01');

Result:

+------------------------+
| YEARWEEK('2023-01-01') |
+------------------------+
|                 202301 |
+------------------------+

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.

Here’s what happens if we move forward a year:

SELECT YEARWEEK('2024-01-01');

Result:

+------------------------+
| YEARWEEK('2024-01-01') |
+------------------------+
|                 202353 |
+------------------------+

As with the WEEK() function, the YEARWEEK() function accepts an optional second argument to specify the mode. The actual result you get from this function will depend on the mode being used. If the mode argument is omitted, the value of the default_week_format system variable is used.

See How YEARWEEK() Works in MariaDB for more on the modes and examples of each.