How the DAYOFWEEK() function works in Mariadb?

he DAYOFWEEK() function is a date and time function that returns the day of the week for a given date.

Posted on

The DAYOFWEEK() function is a date and time function that returns the day of the week for a given date. The return value is an integer from 1 to 7, where 1 represents Sunday, 2 represents Monday, and so on. The DAYOFWEEK() function can be used to perform various calculations and operations based on the day of the week.

Syntax

The syntax of the DAYOFWEEK() function is as follows:

DAYOFWEEK(date)

The date parameter can be any valid date or datetime expression that can be converted to a date. If the date parameter is invalid or null, the function returns null.

Examples

Here are some examples of using the DAYOFWEEK() function in Mariadb.

Example 1: Get the day of the week for a specific date

The following query uses the DAYOFWEEK() function to get the day of the week for the date ‘2024-01-14’.

SELECT DAYOFWEEK('2024-01-14') AS day_of_week;

The output is:

+-------------+
| day_of_week |
+-------------+
|           1 |
+-------------+

This means that the date ‘2024-01-14’ is a Sunday.

Example 2: Get the name of the day of the week for a specific date

The following query uses the DAYOFWEEK() function along with the DAYNAME() function to get the name of the day of the week for the date ‘2024-01-14’.

SELECT DAYNAME('2024-01-14') AS day_name;

The output is:

+----------+
| day_name |
+----------+
| Sunday   |
+----------+

The DAYNAME() function returns the name of the day of the week for a given date, based on the current language setting.

Example 3: Get the number of Sundays in a month

The following query uses the DAYOFWEEK() function along with the COUNT() function and the WHERE clause to get the number of Sundays in January 2024.

SELECT COUNT(*) AS number_of_sundays
FROM (
  SELECT DATE('2024-01-01') + INTERVAL n DAY AS date
  FROM (
    SELECT 0 AS n UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13 UNION ALL
    SELECT 14 UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17 UNION ALL
    SELECT 18 UNION ALL
    SELECT 19 UNION ALL
    SELECT 20 UNION ALL
    SELECT 21 UNION ALL
    SELECT 22 UNION ALL
    SELECT 23 UNION ALL
    SELECT 24 UNION ALL
    SELECT 25 UNION ALL
    SELECT 26 UNION ALL
    SELECT 27 UNION ALL
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30
  ) AS t
  WHERE DATE('2024-01-01') + INTERVAL n DAY <= LAST_DAY('2024-01-01')
) AS t
WHERE DAYOFWEEK(date) = 1;

The output is:

+------------------+
| number_of_sundays|
+------------------+
|                5 |
+------------------+

This query uses a subquery to generate a list of dates from ‘2024-01-01’ to ‘2024-01-31’, and then filters out the dates that are not Sundays using the DAYOFWEEK() function and the WHERE clause. The COUNT() function returns the number of rows that match the condition.

Example 4: Get the first and last day of the week for a specific date

The following query uses the DAYOFWEEK() function along with the DATE_SUB() and DATE_ADD() functions to get the first and last day of the week for the date ‘2024-01-14’.

SELECT DATE_SUB('2024-01-14', INTERVAL DAYOFWEEK('2024-01-14') - 1 DAY) AS first_day_of_week,
       DATE_ADD('2024-01-14', INTERVAL 7 - DAYOFWEEK('2024-01-14') DAY) AS last_day_of_week;

The output is:

+------------------+-----------------+
| first_day_of_week| last_day_of_week|
+------------------+-----------------+
| 2024-01-14       | 2024-01-20      |
+------------------+-----------------+

This query uses the DAYOFWEEK() function to get the number of days to subtract or add from the given date to get the first and last day of the week. The DATE_SUB() and DATE_ADD() functions perform the date arithmetic.

Example 5: Get the number of weekdays in a month

The following query uses the DAYOFWEEK() function along with the SUM() function and the CASE expression to get the number of weekdays (Monday to Friday) in January 2024.

SELECT SUM(CASE WHEN DAYOFWEEK(date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS number_of_weekdays
FROM (
  SELECT DATE('2024-01-01') + INTERVAL n DAY AS date
  FROM (
    SELECT 0 AS n UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10 UNION ALL
    SELECT 11 UNION ALL
    SELECT 12 UNION ALL
    SELECT 13 UNION ALL
    SELECT 14 UNION ALL
    SELECT 15 UNION ALL
    SELECT 16 UNION ALL
    SELECT 17 UNION ALL
    SELECT 18 UNION ALL
    SELECT 19 UNION ALL
    SELECT 20 UNION ALL
    SELECT 21 UNION ALL
    SELECT 22 UNION ALL
    SELECT 23 UNION ALL
    SELECT 24 UNION ALL
    SELECT 25 UNION ALL
    SELECT 26 UNION ALL
    SELECT 27 UNION ALL
    SELECT 28 UNION ALL
    SELECT 29 UNION ALL
    SELECT 30
  ) AS t
  WHERE DATE('2024-01-01') + INTERVAL n DAY <= LAST_DAY('2024-01-01')
) AS t;

The output is:

+-------------------+
| number_of_weekdays|
+-------------------+
|                23 |
+-------------------+

This query uses a similar subquery as the previous example to generate a list of dates from ‘2024-01-01’ to ‘2024-01-31’, and then uses the CASE expression to assign a value of 1 or 0 to each date depending on whether it is a weekday or not. The SUM() function returns the total of the values.

There are some other functions that are related to the DAYOFWEEK() function in Mariadb. Here are some of them:

  • The WEEKDAY() function returns the weekday index for a given date, where 0 represents Monday, 1 represents Tuesday, and so on. For example, WEEKDAY('2024-01-14') returns 6, which means that the date ‘2024-01-14’ is a Sunday.
  • The WEEK() function returns the week number for a given date, based on a specified mode. The mode determines how the first week of the year is calculated, and whether the week starts on Sunday or Monday. For example, WEEK('2024-01-14', 0) returns 2, which means that the date ‘2024-01-14’ is in the second week of the year, and the week starts on Sunday.
  • The DAYOFYEAR() function returns the day of the year for a given date, where 1 represents January 1, 2 represents January 2, and so on. For example, DAYOFYEAR('2024-01-14') returns 14, which means that the date ‘2024-01-14’ is the 14th day of the year.

Conclusion

The DAYOFWEEK() function is a useful function that can help you perform various calculations and operations based on the day of the week for a given date. You can use it along with other date and time functions to manipulate and format dates in Mariadb. I hope this article has helped you understand how the DAYOFWEEK() function works in Mariadb.