How the WEEKOFYEAR() function works in Mariadb?

The WEEKOFYEAR() function in MariaDB is used to return the week number for a given date. This function considers the week containing January 1 as the first week of the year, and returns a number from 1 to 53.

Syntax

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

WEEKOFYEAR(date)

Where date is the date or datetime value from which you want to determine the week number.

Examples

Example 1: Basic Usage of WEEKOFYEAR()

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

SELECT WEEKOFYEAR('2024-01-01') AS week_number;

Below is the output for the statement:

+-------------+
| week_number |
+-------------+
|           1 |
+-------------+

This indicates that January 1, 2024, falls in the first week of the year.

Example 2: Using WEEKOFYEAR() with a Table

Let’s create a table with dates and use the WEEKOFYEAR() function to find their corresponding 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, WEEKOFYEAR(date_value) AS week_number FROM example_dates;

Below is the output for the statement:

+------------+-------------+
| date_value | week_number |
+------------+-------------+
| 2024-01-01 |           1 |
| 2024-07-01 |          27 |
| 2024-12-31 |           1 |
+------------+-------------+

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

Example 3: WEEKOFYEAR() with Current Date

To find the week number of the current date:

SELECT WEEKOFYEAR(CURDATE()) AS week_number;

The output will show the week number for today’s date.

Example 4: WEEKOFYEAR() in a WHERE Clause

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

SELECT * FROM example_dates WHERE WEEKOFYEAR(date_value) = 52;

This will return rows where the date_value falls in the 52nd week of the year.

Example 5: WEEKOFYEAR() with ORDER BY

Sorting records by the week number:

SELECT * FROM example_dates ORDER BY WEEKOFYEAR(date_value);

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

Related Functions

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

• MariaDB YEARWEEK() function returns the year and week number for a given date.
• MariaDB WEEK() function returns the week number for a given date, with options to define the start of the week and whether the week is returned as 0-53 or 1-53.
• MariaDB DAYOFYEAR() function returns the day of the year for a given date.

Conclusion

The WEEKOFYEAR() function is a useful tool in MariaDB for working with date-related data, especially when you need to organize or analyze data on a weekly basis. With the examples provided, you should be able to apply this function effectively in your database queries.