How the WEEKDAY() function works in Mariadb?

The WEEKDAY() function in MariaDB is a date function that returns the index of the day of the week for a given date.

Posted on

The WEEKDAY() function in MariaDB is a date function that returns the index of the day of the week for a given date. The index is zero-based, meaning that Monday is represented by 0, Tuesday by 1, and so on, up to Sunday, which is represented by 6.

Syntax

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

WEEKDAY(date)

Where date is a date or datetime value from which you want to extract the weekday index.

Examples

Example 1: Basic Usage of WEEKDAY()

This example demonstrates how to retrieve the weekday index for a specific date.

SELECT WEEKDAY('2024-03-10') AS weekday_index;

Below is the output for the statement:

+---------------+
| weekday_index |
+---------------+
|             6 |
+---------------+

This indicates that the date ‘2024-03-10’ is a Sunday.

Example 2: Using WEEKDAY() with a Table

First, we’ll create a table with some dates and then use the WEEKDAY() function to find out the weekdays.

DROP TABLE IF EXISTS example_dates;
CREATE TABLE example_dates (date_value DATE);
INSERT INTO example_dates VALUES ('2024-03-11'), ('2024-03-12'), ('2024-03-13');

Now, let’s query the table:

SELECT date_value, WEEKDAY(date_value) AS weekday_index FROM example_dates;

Below is the output for the statement:
+------------+---------------+
| date_value | weekday_index |
+------------+---------------+
| 2024-03-11 |             0 |
| 2024-03-12 |             1 |
| 2024-03-13 |             2 |
+------------+---------------+

The output shows the weekday index for each date in the table.

Example 3: WEEKDAY() with Current Date

To find the weekday index of the current date:

SELECT WEEKDAY(CURDATE()) AS weekday_index;

The output will show the weekday index for today’s date.

Example 4: WEEKDAY() in a WHERE Clause

You can also use the WEEKDAY() function in a WHERE clause to filter results based on the day of the week.

SELECT * FROM example_dates WHERE WEEKDAY(date_value) = 5;

This will return rows where the date_value is a Saturday.

Example 5: WEEKDAY() with ORDER BY

Sorting records by the day of the week:

SELECT * FROM example_dates ORDER BY WEEKDAY(date_value);

This will sort the results starting with Monday and ending with Sunday.

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

  • MariaDB DAYNAME() function returns the name of the weekday for a given date.
  • MariaDB DAYOFWEEK() function returns the index of the day of the week (1=Sunday, 2=Monday, …, 7=Saturday).
  • MariaDB DAYOFYEAR() function returns the day of the year for a given date.

Conclusion

Understanding the WEEKDAY() function in MariaDB is essential for working with date and time data. It allows for easy retrieval of the day of the week from a date and can be used in various scenarios, such as reporting, data analysis, and more. With the examples provided, you should now have a good grasp of how to use the WEEKDAY() function in your queries.