SQL Server - how to dynamically determine financial year?

Question

Every year I have to update my company's financial reports to include the new financial year (as the year isn't coterminus with the calendar year), so I do.....

Case
    when ST_date >= '1996.11.01 00:00:00' and st_date  < '1997.11.01 00:00:00' 
    then '96-97'
[etc]
end as year,

Every year I have to remember which reports I need to amend - most years I forget one!

...Is there a simple dynamic way to determine this?

Answer 1

You could definitely write a simple stored function in SQL Server to determine the financial year based on the date:

CREATE FUNCTION dbo.GetFinancialYear (@input DATETIME)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @FinYear VARCHAR(20)

    SET @FinYear = 
        CASE
            WHEN @INPUT >= '19961101' AND @input < '19971101' THEN '96-97'
            WHEN @INPUT >= '19971101' AND @input < '19981101' THEN '97-98'
            ELSE '(other)'
        END

    RETURN @FinYear
END

and then just use that in all your queries.

SELECT 
    somedate, dbo.GetFinancialYear(somedate)
......

If you need to add a new financial year - just update the one function, and you're done !

Update: if you want to make this totally dynamic, and you can rely on the fact that the financial year always starts on Nov 1 - then use this approach instead:

CREATE FUNCTION dbo.GetFinancialYear (@input DATETIME)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @FinYear VARCHAR(20)

    DECLARE @YearOfDate INT

    IF (MONTH(@input) >= 11)
        SET @YearOfDate = YEAR(@input)  
    ELSE
        SET @YearOfDate = YEAR(@input) - 1

    SET @FinYear = RIGHT(CAST(@YearOfDate AS CHAR(4)), 2) + '-' + RIGHT(CAST((@YearOfDate + 1) AS CHAR(4)), 2)

    RETURN @FinYear
END

This will return:

• 05/06 for a date such as 2005-11-25
• 04/05 for a date such as 2005-07-25

Answer 2

Have a look at this example:

declare @ST_Date datetime = '20120506'

SELECT
    convert(char(2),DateAdd(m,-10,@ST_DATE),2)+'-'+
    convert(char(2),DateAdd(m,+ 2,@ST_DATE),2) as year

As a column expression:

    convert(char(2),DateAdd(m,-10,ST_DATE),2)+'-'+
    convert(char(2),DateAdd(m,+ 2,ST_DATE),2) as year

Pretty trivial!

The way I handle these problems (financial year, pay period etc) is to recognize the fact that financial years are the same as any year, except they start X months later. The straightforward solution is therefore to shift the FY by the number of months back to the calendar year, from which to do any "annual" comparisons or derivation of "year" (or "month").