Search

Thursday, September 8, 2011

SQL Server Denali – EOMONTH()


EOMONTH() returns the date value that represents the last day of the month for given date.
Syntax: EOMONTH( start_date [, months_to_add] )
Before Denali, to calculate the date value that represents the last day of the month we used:
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
In Denali, we can use EOMONTH function.

SELECT EOMONTH (GETDATE()) AS LastDay1, EOMONTH('2011-09-05') AS LastDay2
If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.
SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth, EOMONTH(GETDATE()) AS LastDayOfMonth, EOMONTH (GETDATE(),1)LastDayOfNextMonth

No comments:

Post a Comment