SDU Tools: StartOfFinancialYear, EndOfFinancialYear

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:

http://sqldownunder.com/sdu-tools

 

Leave a Reply

Your email address will not be published. Required fields are marked *