MySQL – Finding First day and Last day of a Month

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 (http://blog.SQLAuthority.com)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s