MySQL WEEKDAY Function

MySQL WEEKDAY Function

The WEEKDAY() function in MySQL is used to determine the weekday index of a given date. The index starts at 0 for Monday and ends at 6 for Sunday.

Syntax

WEEKDAY(date)

• date: The date for which the weekday index needs to be calculated. It can be a column, a date literal, or an expression that evaluates to a date.

How It Works

• The function returns:
  • 0 for Monday
  • 1 for Tuesday
  • 2 for Wednesday
  • 3 for Thursday
  • 4 for Friday
  • 5 for Saturday
  • 6 for Sunday

Examples

1. Basic Usage

Find the weekday index of a specific date:

SELECT WEEKDAY('2025-01-24') AS weekday_index;

Output: 4
(2025-01-24 is a Friday)

2. Using with a Table Column

Calculate the weekday index for dates in a table:

SELECT order_id, order_date, WEEKDAY(order_date) AS weekday_index
FROM orders;

3. Combining with Conditional Logic

Group orders by weekdays:

SELECT WEEKDAY(order_date) AS weekday_index, COUNT(*) AS total_orders
FROM orders
GROUP BY WEEKDAY(order_date);

4. Display Weekday Names Using CASE

Map the weekday index to names:

SELECT order_date,
       CASE WEEKDAY(order_date)
           WHEN 0 THEN 'Monday'
           WHEN 1 THEN 'Tuesday'
           WHEN 2 THEN 'Wednesday'
           WHEN 3 THEN 'Thursday'
           WHEN 4 THEN 'Friday'
           WHEN 5 THEN 'Saturday'
           WHEN 6 THEN 'Sunday'
       END AS weekday_name
FROM orders;

Comparison with Other Date Functions

Function	Description
DAYOFWEEK()	Returns the weekday index (1 = Sunday, 7 = Saturday).
WEEKDAY()	Returns the weekday index (0 = Monday, 6 = Sunday).
DAYNAME()	Returns the full name of the weekday (e.g., "Monday", "Tuesday").

Use Cases

1. Reporting and Analytics: Identify patterns based on the day of the week (e.g., sales trends).

2. Custom Weekday Names: Map weekday indices to custom names for specific regions or business rules.

3. Filtering Data: Extract data for specific days of the week using conditions like WEEKDAY(date) = 4.

Conclusion

The WEEKDAY() function in MySQL is a simple and effective tool for working with weekday indices. Its compatibility with other date functions and conditional logic makes it valuable for date-related analytics and reporting.