[SOLVED] SQL Deriving Financial year (20XX/YY) from dd/mm/yyyy SSMS 2012

SQL Deriving Financial year (20XX/YY) from dd/mm/yyyy SSMS 2012

My financial year is 01/04/20X0 to 31/03/20X1. So current financial year runs from 01/04/2018 to 31/03/2019. This is abbreviated to "20X0/X1" or "2018/19".

My output date is 01/08/2021 and is always of the format dd/mm/yyyy. This corresponds to Financial Year "2021/22"

I need help deriving a formula that is foolproof to return the financial year from my date or any given date of the format dd/mm/yyyy.

I do not want to use a Case statement as there will be several bands and does not seem efficient.

This is currently what I am using but is faulty

CAST(YEAR(DATEADD(month, 9, ISNULL(msdate5.[Completion - Revised], msdate5.[Completion - Original])))-1 as nvarchar) + '/' + CAST(YEAR(DATEADD(month, 9, ISNULL( msdate5.[Completion - Revised],  msdate5.[Completion - Original]))) as nvarchar)

Solution

You can use this expression:

select (datename(year, dateadd(month, -3, @mydate)) + '/' +
        datename(year, dateadd(month, -3 + 12, @mydate))
       )

This offsets the date by three months and extracts the year. Note the use of datename() so you don't have to bother with converting anything to a string.