MySQL – Finding First day and Last day of a Month

MySQL - Finding First day and Last day of a Month aprilmonth 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)

MySQL, SQL DateTime
Previous Post
SQL SERVER – What is SSAS Tabular Data Model and Why to Use it
Next Post
SQL SERVER – Finding Jobs Shrinking Database Files – Notes from the Field #023

Related Posts

7 Comments. Leave new

  • What about
    SELECT DATE_FORMAT(@date,’%Y-%m-01′) AS first_day;
    ?

    Reply
  • Faizan Younus
    April 8, 2015 5:51 pm

    select date_add(LAST_DAY(date_add(now(),interval -2 month)), interval 1 day) FirstDay, LAST_DAY(date_add(now(),interval -1 month)) LastDay;

    Reply
  • Abhishek Bhadauria
    December 7, 2016 3:53 pm

    SET @FirstDayOfMonth = DATE_SUB(DATE(NOW()),INTERVAL (DAY(NOW())-1) DAY);
    SET @LastDayOfMonth = LAST_DAY(NOW());

    Reply
  • Lane O'Connor
    May 18, 2018 1:59 am

    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

    Reply
  • Laszlo Lieszkovszky
    November 16, 2019 9:20 pm

    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;

    Reply
  • SET @START=LAST_DAY(CURDATE()-INTERVAL 2 MONTH)+INTERVAL 1 DAY;
    SET @END=LAST_DAY(CURDATE()-INTERVAL 1 MONTH);

    Reply

Leave a Reply