Easily Calculating End of Month
Published: 21st September 2014
Something I’ve had to do a lot recently is to calculate financials which involved using an end of month date. This has always been a pain to calculate in SQL Server. There are a lot of ways of doing it, but all are annoying.

Luckily that’s now changed, but I found that a good few people didn’t seem to know that.

SQL Server 2012 provides a function that will do this for you… making life much simpler if you use the figures as much as I have recently and need to calculate them on a fly and don’t want to use a pesky UDF in the process.

Basically if you want to calculate the end of month you’re pretty much left with one option… you take the date you want, add a month, replace the day as 1 and then minus a day.

Effectively you’re doing some form of the following:

-- we'll use 21st September
declare @date date = '2014-09-21'

-- add a month, taking us to 2014-10-21
select @date = dateadd(mm, 1, @date)

-- remove the day and replace with 01
select @date = convert(date, convert(varchar(4), year(@date)) + '-' + convert(varchar(2), month(@date)) + '-01')

-- minus 1 day to give us the last date of the month we're interested in
select dateadd(dd, -1, @date)

-- Result = '2014-09-30'


So how does SQL 2012 help us? Well it provides an End of Month function… EOMONTH… this makes everything just that much simpler:

-- we'll use 21st September
declare @date date = '2014-09-21'
select eomonth(@date)


Nice and easy, yes?
Comments:
NB: Comments will only appear once they have been moderated.