Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Easily Calculating End of Month
Published: Apr 15, 2016
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?
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.