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.
Tips When You Work With the Last Day of a Month in MySQL
The nice part about LAST_DAY is that it already knows the calendar. It returns February 29 in a leap year and February 28 in other years, so you never need your own table of month lengths. If you pass it a DATETIME, it returns only the date, and if the value is not a valid date, it returns NULL.
One mistake I see often is filtering a month with BETWEEN the first and the final date of that month. If the column stores a time as well, every row after midnight on the final day is left out. A safer filter says: the value is greater than or equal to the first day of the month, and less than the first day of the next month. Both methods above give you those two dates easily.
If you also work with SQL Server, the same idea exists there as EOMONTH, available from SQL Server 2012 onwards. It even takes an optional second argument to move forward or back by a number of months.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
What about
SELECT DATE_FORMAT(@date,’%Y-%m-01′) AS first_day;
?
+1 Simplest is always the best.
select date_add(LAST_DAY(date_add(now(),interval -2 month)), interval 1 day) FirstDay, LAST_DAY(date_add(now(),interval -1 month)) LastDay;
SET @FirstDayOfMonth = DATE_SUB(DATE(NOW()),INTERVAL (DAY(NOW())-1) DAY);
SET @LastDayOfMonth = LAST_DAY(NOW());
How about using the input date (current date in my example below) and replacing the day value with “01”?
select concat(left(curdate(),7),”-01″) as “Last Date of This Month”
Lane
We compared various methodologies on our blog, seems like it doesn’t really matter what methodology you use they are all super fast.
The fastest solution by far – albeit one that only returns a string and not a date/datetime object – is the one mentioned by Lane O’Connor above:
SELECT CONCAT(LEFT(@date, 7), ‘-01’) AS first_day_of_month;
If you need datetime, the fastest solution to that takes abour 3x as long:
SELECT TIMESTAMP(CONCAT(LEFT(@date, 7), ‘-01’)) AS first_day_of_month;
Getting a DATE takes the longest at about 5x as long (as just VARCHAR):
SELECT CAST(CONCAT(LEFT(@date, 7), ‘-01’) AS DATE) AS first_day_of_month;
SET @START=LAST_DAY(CURDATE()-INTERVAL 2 MONTH)+INTERVAL 1 DAY;
SET @END=LAST_DAY(CURDATE()-INTERVAL 1 MONTH);