T-SQL 101: 74 Find the date for the end of a month in SQL Server T-SQL with EOMONTH

When writing T-SQL, it’s surprising how often you need to calculate the end of month for a given date, and so you want the last date in the month for whatever date you supply. That’s the main thing that the EOMONTH() function does.

So in this case, 16th of February 2020, we might wonder if that is a leap year. So would it be the 28th or the 29th? And so if we execute that, we find the 29th is the date, so this is actually a leap year:

Now this function can also take an optional second parameter. If you put the value 1, it will give you the end of the month after the date you supply. A value of 2 will give you the end of two months later. And so on. You can even give it negative values. That’s actually quite useful.

There are two other comments I’d make on it though:

There’s no equivalent BOMONTH (beginning of month). I think that’s really a little strange.

The other thing is I actually don’t like the name of the function at all. I’d really prefer it was ENDOFMONTH (or even END_OF_MONTH -> that’s another thing the SQL Server team have never been consistent on. Some functions and object names have underscores between words, some don’t).

There’s no rational argument for saving those three characters, and for me, it’s just an unnecessary abbreviation. I’m told the reason they did that is that’s the name of the function in Excel. That doesn’t sound a great reason to me and look, I’d encourage you, whenever you’re writing code yourself, spell out names in full unless there’s a really strong reason to abbreviate them.

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-06-15