T-SQL 101: 61 Calculating end of month in SQL Server T-SQL

It’s surprising how often you need to calculate the date for the end of a month i.e. you supply a date, and you want the date for the end of the month that contains that date. The EOMONTH function does that.

Look at the query here:

And the result here:

It might seem really easy to calculate but there are tricky aspects. For example, for a date in February, is it a leap year? You can see from the results that 2020 is a leap year.

The EOMONTH function can do more than this. If I supply a second optional parameter, I can find the end of the month for surrounding months. If I say

SELECT EOMONTH(‘20200216’, -1)

I will see the end of the previous month. A parameter of 1 would give the next month. A parameter of 2 would give the end of April.

One final note is that I don’t like the name of this function. I really with it just was ENDOFMONTH. There’s no need to make it less clear by saving those 3 characters. There are many other functions that have very long names, so why shorten this one?

I’m told that the reason they did this is that that’s the name of the function in Excel. To me, that’s a poor reason. I’d encourage you that whenever you’re writing code yourself, that you spell out names in full unless there’s a strong reason to abbreviate them. (And that’s rare).

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-03-16