How to Find the First Day of the Month for a Given Date in MySQL?

Query to Retrieve the First Day of Each Month corresponding to a Given Date

To obtain the first day of each month corresponding to a specified date, we can utilize MySQL's built-in date manipulation functions.

Query Formulation:

To determine the first day of the month corresponding to a given date, we can apply the following query:

SELECT CAST(DATE_FORMAT(DATE_SUB(?, INTERVAL DAYOFMONTH(?) - 1 DAY), '%Y-%m-01') AS DATE);

where:

• ? represents the date value specified by the user (e.g., '2010-06-15')

Query Explanation:

• DATE_SUB(?, INTERVAL DAYOFMONTH(?) - 1 DAY): This expression calculates the date one day before the first day of the month corresponding to the given date.
• DAYOFMONTH(?) - 1: This expression subtracts one from the day of the month to obtain the last day of the previous month.
• %Y-%m-01 is a string literal specifying the desired date format, which includes the year, month, and day (set to the first day of the month).

To illustrate the query's functionality:

• Input: '2010-06-15'
• Output: '2010-06-01' (desired first day of the month)

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