MySQL DAYOFWEEK Function

MySQL DAYOFWEEK Function

The DAYOFWEEK function in MySQL returns the weekday index for a given date. The index ranges from 1 (Sunday) to 7 (Saturday), aligning with the U.S. convention for weeks starting on Sunday.

Syntax

DAYOFWEEK(date)

• date: A valid date or datetime expression from which the weekday index is derived.

How It Works

1. If the provided date is valid, the function returns an integer value representing the day of the week.
2. If the input date is NULL, the function returns NULL.
3. For invalid dates, MySQL generates an error.

Examples

1. Get the Day of the Week for a Specific Date

SELECT DAYOFWEEK('2025-01-26') AS day_of_week;

Output:

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

The output 1 indicates that January 26, 2025, is a Sunday.

2. Use with CURDATE

SELECT DAYOFWEEK(CURDATE()) AS today_day_of_week;

Output:
The result depends on the current date.

3. Handle Invalid Dates

SELECT DAYOFWEEK('2025-02-30') AS day_of_week;

Output:
This will result in an error because 2025-02-30 is not a valid date.

4. Using DAYOFWEEK with a Table

Assume you have a table events:

event_id	event_date
1	2025-01-25
2	2025-01-26
3	2025-01-27

Query to retrieve the weekday index for each event date:

SELECT event_id, event_date, DAYOFWEEK(event_date) AS day_of_week
FROM events;

Output:

+----------+------------+-------------+
| event_id | event_date | day_of_week |
+----------+------------+-------------+
| 1        | 2025-01-25 | 7           |
| 2        | 2025-01-26 | 1           |
| 3        | 2025-01-27 | 2           |
+----------+------------+-------------+

Practical Use Cases

1. Scheduling: Determine which day of the week an event occurs.
2. Reports: Generate reports grouped by day of the week.
3. Validation: Ensure that certain dates fall on specific weekdays (e.g., weekends).

Notes

• The DAYOFWEEK function uses the U.S. standard for week structure, where Sunday is the first day (index 1).
• If you need the weekday name (e.g., "Monday"), use the DAYNAME function instead.
• Combine with other date functions for advanced date manipulations.

Conclusion

The DAYOFWEEK function in MySQL is a straightforward tool to determine the weekday index of a given date. It is helpful for tasks involving date validation, scheduling, and reporting where the day of the week plays a role.