SQL Server Portal

“How to calculate the last date of the current, previous & next month” ? I have seen this question many times in different blogs and there are multiple approaches that we have been following to find it.
For Example :

--SQL Server 2005/2008
DECLARE @DATE DATETIME
SET @DATE='2012-09-10'

--Last date of the Previous Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE),0)),120) AS [Previous Month]

--Last date of the Current Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)),120) AS [Current Month]

--Last date of the Next Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+2,0)),120) AS [Next Month]

Previous Month
--------------
2012-08-31

(1 row(s) affected)

Current Month
-------------
2012-09-30

(1 row(s) affected)

Next Month
-----------
2012-10-31

(1 row(s) affected)

In SQL Server 2012, the solution came as a new built in function namely EOMONTH. Lets discuss this function syntax, parameters, purpose and examples in detail.

Syntax :

EOMONTH ( start_date [, month_to_add ] )

Parameters :
@start_date : A valid Date in varchar or date time data type (Mandatory)
@month_to_add : A valid integer for month range from 0-9999 (Optional). By default it is zero.

Purpose :
This function requires a valid date (Either in Datetime or varchar data type) as a parameter and returns last date of that month. There is an optional parameter called “@month_to_add”. By using this optional parameter, we can get the last date of other months also. It could be previous, next or any month.

Let me explain this with simple examples.

Example-1 : EOMONTH – valid start date as Date data type

Declare @Date as Date ='2012-09-01'

--Last date of the Previous Month
Select EOMONTH (@Date,-1) as [Previous Month]

--Last date of the current Month
Select EOMONTH (@Date) as [Current Month]

--Last date of the Next Month
Select EOMONTH (@Date,1) as [Next Month]

Previous Month
--------------
2012-08-31

(1 row(s) affected)

Current Month
-------------
2012-09-30

(1 row(s) affected)

Next Month
----------
2012-10-31

(1 row(s) affected)

Example-2 : EOMONTH – valid start date as varchar data type

Declare @Date as varchar(10) ='2012-09-01'

--Last date of the current Month
Select EOMONTH (@Date) as [Current Month]

Current Month
-------------
2012-09-30

(1 row(s) affected)

Example-3 : EOMONTH – Last Date of each month in the Current Year

Declare @Date as Date ='2012-01-01'

--Last date of the Jan Month
Select EOMONTH (@Date) as [Jan]

--Last date of the Feb Month
Select EOMONTH (@Date,1) as [Feb]

--Last date of the Mar Month
Select EOMONTH (@Date,2) as [Mar]

--Last date of the Apr Month
Select EOMONTH (@Date,3) as [Apr]

--Last date of the May Month
Select EOMONTH (@Date,4) as [May]

--Last date of the Jun Month
Select EOMONTH (@Date,5) as [Jun]

--Last date of the Jul Month
Select EOMONTH (@Date,6) as [Jul]

--Last date of the Aug Month
Select EOMONTH (@Date,7) as [Aug]

--Last date of the Sep Month
Select EOMONTH (@Date,8) as [Sep]

--Last date of the Oct Month
Select EOMONTH (@Date,9) as [Oct]

--Last date of the Nov Month
Select EOMONTH (@Date,10) as [Nov]

--Last date of the Dec Month
Select EOMONTH (@Date,11) as [Dec]

Jan
----------
2012-01-31

(1 row(s) affected)

Feb
----------
2012-02-29

(1 row(s) affected)

Mar
----------
2012-03-31

(1 row(s) affected)

Apr
----------
2012-04-30

(1 row(s) affected)

May
----------
2012-05-31

(1 row(s) affected)

Jun
----------
2012-06-30

(1 row(s) affected)

Jul
----------
2012-07-31

(1 row(s) affected)

Aug
----------
2012-08-31

(1 row(s) affected)

Sep
----------
2012-09-30

(1 row(s) affected)

Oct
----------
2012-10-31

(1 row(s) affected)

Nov
----------
2012-11-30

(1 row(s) affected)

Dec
----------
2012-12-31

(1 row(s) affected)

Reference : MSDN