How the YEARWEEK() function works in Mariadb?

The YEARWEEK() function in MariaDB is a date function that returns the year and week number for a given date. The result is an integer with the format YYYYWW where YYYY is the year and WW is the week number.

Syntax

The syntax for the MariaDB YEARWEEK() function is as follows:

YEARWEEK(date, mode)

The date parameter is the date or datetime value from which you want to extract the year and week. The optional mode parameter affects how the week number is calculated.

Examples

Example 1: Basic Usage of YEARWEEK()

This example shows how to get the year and week number for a specific date.

SELECT YEARWEEK('2024-03-10') AS yearweek;

Below is the output for the statement:

+----------+
| yearweek |
+----------+
|   202410 |
+----------+

This indicates that the date ‘2024-03-10’ falls in the 10th week of the year 2024.

Example 2: Using YEARWEEK() with a Table

Let’s create a table with dates and use the YEARWEEK() function to find their corresponding year and week numbers.

DROP TABLE IF EXISTS example_dates;
CREATE TABLE example_dates (date_value DATE);
INSERT INTO example_dates VALUES ('2024-01-01'), ('2024-07-01'), ('2024-12-31');

Now, we query the table:

SELECT date_value, YEARWEEK(date_value) AS yearweek FROM example_dates;

Below is the output for the statement:

+------------+----------+
| date_value | yearweek |
+------------+----------+
| 2024-01-01 |   202353 |
| 2024-07-01 |   202426 |
| 2024-12-31 |   202452 |
+------------+----------+

The output shows the year and week number for each date in the table.

Example 3: YEARWEEK() with Mode Parameter

To specify the mode for calculating the week number:

SELECT YEARWEEK('2024-03-10', 1) AS yearweek;

Below is the output for the statement:

+----------+
| yearweek |
+----------+
|   202410 |
+----------+

This output may differ from the default mode, depending on the date and mode used.

Example 4: YEARWEEK() in a WHERE Clause

You can use the YEARWEEK() function in a WHERE clause to filter results based on the year and week number.

SELECT * FROM example_dates WHERE YEARWEEK(date_value) = 202426;

Below is the output for the statement:

+------------+
| date_value |
+------------+
| 2024-07-01 |
+------------+

This will return rows where the date_value falls in the 27th week of the year 2024.

Example 5: YEARWEEK() with ORDER BY

Sorting records by the year and week number:

SELECT * FROM example_dates ORDER BY YEARWEEK(date_value);

Below is the output for the statement:

+------------+
| date_value |
+------------+
| 2024-01-01 |
| 2024-07-01 |
| 2024-12-31 |
+------------+

This will sort the results starting with the earliest year and week number.

Related Functions

Below are a few functions related to the MariaDB YEARWEEK() function:

• MariaDB WEEK() function returns the week number for a given date.
• MariaDB YEAR() function extracts the year from a date.
• MariaDB DATE_FORMAT() function formats the date value according to the format string.

Conclusion

The YEARWEEK() function is a valuable tool for organizing and analyzing data on a weekly basis throughout the year. It is particularly useful for generating reports, managing events, and performing time-series analysis. With the examples provided, you should be well-equipped to use the YEARWEEK() function in your database queries to extract meaningful insights from date-related data.