How to Get the First Day of a Month in MySQL?

How to Get First Day of the Corresponding Month in MySQL

Given a user-selected date, it's often necessary to calculate the first day of the corresponding month for various analytical purposes. This brief tutorial presents a MySQL solution to this common problem.

Understanding the Problem:

The query provided attempts to retrieve the first day of July 2010. However, it doesn't provide the desired result because it subtracts the day of the month (in this case, 17) from the selected date and adds 1 day. This calculation would not be correct for other user-selected dates.

The MySQL Solution:

To obtain the first day of the corresponding month for any given date, use the following MySQL query:

select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);

Explanation:

• NOW(): Retrieves the current system date and time.
• DATE_FORMAT(): Formats the date part of the NOW() result using the '%Y-%m-01' format, where '%Y' represents the year, '%m' the month, and '01' ensures the first day of the month.
• CAST(): Converts the formatted date string back to a DATE datatype for proper storage in the database.

Using this query with any user-selected date as an input will consistently return the first day of the corresponding month, fulfilling the specified requirement.

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