How to Select Records for a Specific Week in MySQL: Monday to Sunday or Sunday to Saturday?

MySQL: Selecting Records for a Week

Initial Problem:
How to retrieve records from a table for a specific week, given a specified date?

Traditional Algorithm:

1. Determine the day of the week for the specified date.
2. Calculate the number of days to the nearest Monday.
3. Compute the dates for Monday and Sunday of that week.
4. Execute a query to retrieve records within the calculated date range.

Shorter Algorithm (Using MySQL's YEARWEEK() Function):

SELECT *
FROM   your_table
WHERE  YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1)

Explanation:

• The YEARWEEK() function takes a date and an optional parameter (1 in this case) that specifies the first day of the week (Monday).
• By comparing the YEARWEEK() for the specified date (date) to the YEARWEEK() for the current date (CURDATE()), the query selects records that belong to the same week.

Addressing the Additional Question:

The provided query used by the user selects records for the week from Sunday to Saturday, instead of Monday to Sunday. To correct this, the following modification can be made:

SELECT *
FROM   your_table
WHERE  YEARWEEK(`date`, 3) = YEARWEEK(CURDATE(), 3)

• The optional parameter 3 specifies the first day of the week as Monday instead of Sunday.

The above is the detailed content of How to Select Records for a Specific Week in MySQL: Monday to Sunday or Sunday to Saturday?. For more information, please follow other related articles on the PHP Chinese website!