“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
SQL Server Portal 