Finding Month Ends in T-SQL

• The last second of a minute is always 59.
• The last minute of a hour is always 59.
• The last hour of a day is always 23.

But the last day of a month? Well that depends on what month it is. And the year matters too because a leap year means February gets an extra day.

This used to be somewhat tricky to calculate, but then for SQL Server 2012 Microsoft added the EOMONTH() function (End Of MONTH) to T-SQL to help with this. EOMONTH() takes a DATE or DATETIME, and it will return the DATE (with no time component) of the last day of that month. Here's a basic example:

1SELECT EOMONTH('20180219');

It also accounts for leap years. Let's try a date in February 2016:

1SELECT EOMONTH('20160224');

What about other months?

In the case that you need the last day of a different month than the current one, you can make use of EOMONTH()'s second parameter. This allows you to add (or subtract) months from the date you specified:

1-- Second parameter adds that number of months to the date
2SELECT EOMONTH('20180220') AS CurrentMonthEnd,
3       EOMONTH('20180220', -1) AS PreviousMonthEnd,
4       EOMONTH('20180220', 1) AS NextMonthEnd;

What if I need the first day of the month?

There's no similar function to calculate the first day of the month, but think about it: the first day of the month is very predictable. It's always day 1. You can also use EOMONTH() to find the first day of a month by adding one day to the last day of the previous month:

1SELECT DATEADD(DAY, 1, EOMONTH('20180220', -1)) AS FirstDayOfMonth;

What if I don't have SQL Server 2012?

If you're still running SQL Server 2008R2 or older, my first piece of advice is to please look into upgrading to something more current soon. As of this writing, SQL Server 2008R2 Service Pack 3 is only supported until 9 July 2019. If you're not running Service Pack 3, you are already unsupported.

Without the EOMONTH() function, calculating the end of the month gets a bit more complex. There are several ways to do it in T-SQL, but this is my preferred method:

1SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS DATE)

This works by utilizing some creative math with dates. I've highlighted the different sections of the statement to explain what's happening:

What if I don't have a computer?

I've got a solution for this one too! Ever heard of the knuckle trick?

Make two fists and put them next to each other so your knuckles are lined up. Each high and low point along your knuckles represents a month, and the high points represent months with 31 days. Low points are months with less than 31 days (28/29 for February, 30 for the rest).