How to Determine the First Day of the Week in MySQL?

Determining the First Day of the Week in MySQL

When working with date ranges, it's often necessary to identify the first day of the week for a given date. In MySQL, there are different approaches depending on the desired starting day of the week.

Starting the Week on Sunday

To obtain the first day of the week starting on Sunday, use the following formula:

DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)

For example, for the date 2011-01-03, which falls on a Monday, the calculation would be:

DATE_ADD('2011-01-03', INTERVAL(1-DAYOFWEEK('2011-01-03')) DAY)

Result: 2011-01-02 (Sunday)

Starting the Week on Monday

If you want to start the week on Monday, use this formula instead:

DATE_ADD(mydate, INTERVAL(-WEEKDAY(mydate)) DAY)

For the same date (2011-01-03), the calculation would be:

DATE_ADD('2011-01-03', INTERVAL(-WEEKDAY('2011-01-03')) DAY)

Result: 2011-01-03 (Monday)

Applying to a Query

To incorporate the first day of the week calculation into your query to group by weeks, you can replace:

date(date_entered) as week

with:

DATE_ADD(date_entered, INTERVAL(1-DAYOFWEEK(date_entered)) DAY) as week

This will ensure that weeks start on Sunday. If desired, you can adjust the formula to start weeks on Monday.

The above is the detailed content of How to Determine the First Day of the Week in MySQL?. For more information, please follow other related articles on the PHP Chinese website!