How to Find the First Day of a Week in MySQL?

How to Retrieve the First Day of a Week Using MySQL

To obtain the first day of the week for a given date, MySQL provides versatile solutions that enable you to modify your existing query effectively.

Suppose you have a date of "2011-01-03" and wish to determine the first day of the week, which is Sunday, resulting in "2011-01-02." To achieve this, you can use the following methods:

If the Week Starts on Sunday:

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

This formula computes the first day of the week by subtracting the current day of the week (DAYOFWEEK) from 1 and then adding the result to the original date using DATE_ADD.

If the Week Starts on Monday:

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

For a Monday start, WEEKDAY is employed to calculate the number of the day within the week, and this value is subtracted from the original date. The result, when added to the date, yields the Monday of the week.

By incorporating one of these methods into your existing query, you can modify your "week" column selection to obtain the first day of the week, ensuring that your report accurately displays weekly data.

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