Data Warehousing Snippets – Fiscal year function

So, I figured I’d start a little series and post various little (mainly) data warehousing related functions, procedures and other bits of (hopefully) useful T-SQL code. I’ll put these all in a complete Visual Studio SQL project and publish to our GitHub repository (eventually) if you just want to download the whole thing.

Note that for some/most of the series, I will use specific schemas to categorise each function or procedure. This should lead to a nice clean database with a logical and easy to follow structure.

Since our function today is about dates, I’ve created a schema called ‘Chronological‘ to make it clear what this function is for and any other related functions, procedures or tables etc. If you want to create this schema, just run the T-SQL script below on your database. Or if you prefer, alter the function code further down to use another schema, such as ‘dbo‘.

CREATE SCHEMA [Chronological]

So, without further ado, first in the series (and today’s snippet) will about calculating the fiscal year:-

CREATE FUNCTION [Chronological].[FiscalYear] (
	@date DATE
	,@separator VARCHAR(1)
)
RETURNS VARCHAR(7)
AS
BEGIN
	-- first calculate the starting year, if the month is 
	-- is before April then the starting year is the previous year
	-- otherwise its just the current year
	DECLARE @startingFiscalYear INT = CASE 
		WHEN MONTH(@date) < 4 THEN YEAR(@date) - 1
		ELSE YEAR(@date)
	END	

	-- return our fiscal year, note that we just add 1 to 
	-- the starting year to get the ending year
	RETURN CAST(@startingFiscalYear AS VARCHAR(4)) + @separator + RIGHT(CAST(1 + @startingFiscalYear AS VARCHAR(4)), 2)
END

Note that I’ve used a DATE data type for the first parameter, although this function will work if you pass a DATETIME or SMALLDATETIME value. The second parameter is the separator you would like to use, it’s likely this will either be a ‘-‘ or ‘/’ as most fiscal years are display like 2019-20 or 2019/20.

Anyway, that’s it for today. I’ll try and post regular entries in this series so that eventually you should have a decent amount of infrastructure that you could use for all sorts of projects.