I upvoted @StickyBits answer, but wanted to suggest the usage of a Date Dimension table (if you don't already have one) as another option. In addition to providing you the ability to determine the last day of any month, there is a wealth of other information that could also be used once you find a particular row in the Date Dimension.
Check out Aaron Bertrand's post regarding Creating a date dimension or calendar table in SQL Server. Aaron's example Date Dimension starts off with a basic set of columns and then he shows you how to add other attributes.
CREATE TABLE dbo.DateDimension
(
--DateKey INT NOT NULL PRIMARY KEY,
[Date] DATE NOT NULL,
[Day] TINYINT NOT NULL,
DaySuffix CHAR(2) NOT NULL,
[Weekday] TINYINT NOT NULL,
WeekDayName VARCHAR(10) NOT NULL,
IsWeekend BIT NOT NULL,
IsHoliday BIT NOT NULL,
HolidayText VARCHAR(64) SPARSE,
DOWInMonth TINYINT NOT NULL,
[DayOfYear] SMALLINT NOT NULL,
WeekOfMonth TINYINT NOT NULL,
WeekOfYear TINYINT NOT NULL,
ISOWeekOfYear TINYINT NOT NULL,
[Month] TINYINT NOT NULL,
[MonthName] VARCHAR(10) NOT NULL,
[Quarter] TINYINT NOT NULL,
QuarterName VARCHAR(6) NOT NULL,
[Year] INT NOT NULL,
MMYYYY CHAR(6) NOT NULL,
MonthYear CHAR(7) NOT NULL,
FirstDayOfMonth DATE NOT NULL,
LastDayOfMonth DATE NOT NULL,
FirstDayOfQuarter DATE NOT NULL,
LastDayOfQuarter DATE NOT NULL,
FirstDayOfYear DATE NOT NULL,
LastDayOfYear DATE NOT NULL,
FirstDayOfNextMonth DATE NOT NULL,
FirstDayOfNextYear DATE NOT NULL
);
GO
Using DateAdd
to maneuver forward and backwards, you can find a particular row in the Date Dimension table and have access to many additional attributes about a particular date.
SELECT *
FROM dbo.DateDimension
WHERE [Date] = dateadd(month, - 2, convert(DATE, getdate()));