SQL Server 2012 added the function EOMONTH. It returns the end of the month. That's useful but there are two things that bug me about it:
First: What's with the name? Do we really have to save 3 characters from ENDOFMONTH, particularly when the same version added functions like DATETIMEOFFSETFROMPARTS? When I asked, I was told it was the name of the function in Excel. I can't admit to being happy that SQL Server and T-SQL is following the naming of a function that someone added to Excel in the 1980's in some adhoc way.
And while we're on the name, I'd like to plead a little to the team to be consistent. 2017 added FROMPARTS functions too but they have underscores eg: NODE_ID_FROM_PARTS. <sigh>
Second: Why no SOMONTH or BOMONTH (or better STARTOFMONTH)?
Time for more grumpy old dudes again:
Regardless, there's also a need to calculate the start and end of financial years. That's a little trickier because different locations start their financial years at different times. Here in Australia it's July 1st, but when I was working with a US-based company, it was November 1st, and I'm sure there are many variations.
So I added two tools to SDU Tools to calculate these values. You can use them like this:
SELECT SDU_Tools.StartOfFinancialYear('somedate', 7);
SELECT SDU_Tools.EndOfFinancialYear('somedate', 11);
There are two parameters:
- Any date within the financial year
- The month that the financial year starts on.
You can see them in action here:
You can find out more about our free SDU Tools here: