MySQL supports a lot of DATE and TIME related functions. If you want to find out last day of a month, you can make use of an inbuilt function named LAST_DAY.
SET @date:='2012-07-11';
SELECT LAST_DAY(@date) AS last_day;
The above code returns the value 2012-07-31
However, there is no inbuilt function to find out first day for a month. We will see two methods to find out the first day.
Method 1 : Use DATE_ADD and LAST_DAY functions
SET @date:='2012-07-11';
SELECT date_add(date_add(LAST_DAY(@date),interval 1 DAY),interval -1 MONTH) AS first_day;
Result is
first_day ---------- 2012-07-01
The logic is to find last day of a month using LAST_DAY function; Add 1 day to it using DATE_ADD function so that you will get first day of next month; Subtract 1 month from the result so that you will get first day of the current month
Method 2 : Use DATE_ADD and DAY functions
SET @date:='2012-07-11';
SELECT date_add(@date,interval -DAY(@date)+1 DAY) AS first_day;
Result is
first_day ---------- 2012-07-01
The logic is to find the day part of date; add 1 to it and subtract it from the date. The result is the first day of the month.
So you can effectively make use these functions to perform various datetime related logics in MySQL.
Reference: Pinal Dave (https://blog.sqlauthority.com)
