How to get the First Day and the Last Day of the Month in MySQL

Tutorialsrack 30/04/2019 SQL MySQL

In this article, we will learn How to get the First Day and the Last Day of the Month in MySQL.

MySQL supports a lot of DATE and TIME related functions. If you want to find out the last day of a month, you can make use of an inbuilt function named LAST_DAY.

But there is no built-in function to get or to find the first day of the month, so here we use 4 methods to get the first day of the month in MySQL.

Method 1: To Get the First Day of the month using DATE_ADD and LAST_DAY function in MySQL, use the statement given below:

SET @date:= '2019-02-03';
SELECT date_add(date_add(LAST_DAY(@date),interval 1 DAY),interval -1 MONTH) AS 'First Day of the Month';

Method 2: To Get the First Day of the month using DATE_ADD and LAST_DAY function in MySQL, use the statement given below:

SET @date:= '2019-02-03';
SELECT date_add(@date,interval -DAY(@date)+1 DAY) AS 'First Day of the Month';

Method 3: To Get the First Day of the month using DATE_FORMAT function in MySQL, use the statement given below:

SET @date:= '2019-02-03';
SELECT DATE_FORMAT(@date,'%Y-%m-01') AS 'First Day of the Month';

Method 4: To Get the First Day of the month using DATE_SUB function in MySQL, use the statement given below:

set @date:= '2019-02-03';
Select DATE_SUB(DATE(@date), INTERVAL (DAY(@date)-1) DAY) as 'First Day of the Month';

To Get the Last of the Month, Use LAST_DAY function, use the statement given below:

SELECT LAST_DAY('2019-02-03') AS 'Last Day of the Month';

I hope this article will help you to understand How to get the First Day and the Last Day of the Month in MySQL.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts