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)
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);