EOMONTH - New Function in SQL Server 2012

EOMONTH: Returns the last day of the month for the specified date. Also picks up previous or next month's last day of month using an optional argument. So no more expressions with nested date functions :)

Syntax: EOMONTH(<DateColumn>, <month_to_add>)

Previously, we used to run expressions like below for Last Day Of Previous Month, Last Day of Current Month and Last Day of Next Month.

Code:

SELECT

[Last Day of Previous Month] = CONVERT(VARCHAR(10), DATEADD(DD,-(DAY(GETDATE())),GETDATE()), 101),

[Last Day of Current Month] = CONVERT(VARCHAR(10), DATEADD(DD,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())), 101),

[Last Day of Next Month] = CONVERT(VARCHAR(10), DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))), DATEADD(MONTH, 2, GETDATE())), 101)

 ----------------------------------------------------------------------------------------------------------------------

New Code: Using EOMONTH function, the expressions are simple now.

SELECT

EOMONTH(GETDATE(), -1) AS [Last Day Of Previous Month],

EOMONTH(GETDATE()) AS [Last Day Of Current Month],

EOMONTH(GETDATE(), 1) AS [Last Day Of Next Month]

Conclusion: Thanks to Microsoft SQL Server 2012 for making such frequently used expressions into a simple functions. Hope this helps! Happy TSQL'ing.