T-SQL: Get Last Day of Month

One of my early blog posts highlighted how to get the first day of the current month. Well, another common task is get to the last day of a month and i'm (reasonably) pleased to see that SQL Server 2012 has an inbuilt function to achieve this.

In versions previous to SQL2012, this problem can be solved by running the following:

DECLARE @InputDate DATE = GETDATE() 

SELECT DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0))


However, we can now just use the inbuilt function EOMONTH which should make coding easier to read.

DECLARE @InputDate DATE = GETDATE()

SELECT EOMONTH(@InputDate)


-- 2012-02-29 



You can also specify an offset to the function which will allow you to look at months x number of months from your inputdate which I can certainly see being useful.

DECLARE @InputDate DATE = GETDATE()

SELECT EOMONTH(@InputDate, 4)


-- 2012-06-30

Note how the return type is a DATE and not a DATETIME.  Interestingly, Books Online suggests that the return type is "start_date or datetime2(7)"  although my tests suggest it always returns DATE.