MySQL DAYOFWEEK() Function

The DAYOFWEEK() function in MySQL returns the weekday index for a given date, where the result is a number between 1 and 7. This index corresponds to the days of the week, starting from Sunday as 1 and ending with Saturday as 7.

Definition and Usage

• The DAYOFWEEK() function returns an integer representing the day of the week for a given date.
• The result is based on the following mapping:
  • 1 = Sunday
  • 2 = Monday
  • 3 = Tuesday
  • 4 = Wednesday
  • 5 = Thursday
  • 6 = Friday
  • 7 = Saturday

Syntax

DAYOFWEEK(date)

• date: Required. A valid date or datetime value for which the weekday index is to be extracted.

Examples

Example 1: Return the weekday index for a specific date

SELECT DAYOFWEEK("2017-06-15");

Output:

4

In this case, 2017-06-15 is a Thursday, so the function returns 4 as Thursday corresponds to the index 4.

Example 2: Return the weekday index for a datetime value

SELECT DAYOFWEEK("2017-06-15 09:34:21");

Output:

4

The DAYOFWEEK() function returns 4 for this datetime value because the day is Thursday.

Example 3: Return the weekday index for the current system date

SELECT DAYOFWEEK(CURDATE());

Output:

5

If today's date is a Friday, the query will return 5 because Friday corresponds to index 5.

Use Cases

• Day-based calculations: If you need to perform calculations or group data by the day of the week.
• Scheduling and Reports: For systems that need to categorize or report data based on the weekday.
• Conditional Logic: For conditional logic where different actions are taken based on the day of the week.

Related Functions

• DAYOFMONTH(): Returns the day of the month (1-31).
• DAYOFYEAR(): Returns the day of the year (1-366).
• WEEKDAY(): Returns the weekday index for a date, but with a different mapping where Monday is 0 and Sunday is 6.
• DATE_FORMAT(): Formats the date, which can also be used to extract the weekday name or number with custom formatting.