Search code examples
sql-server

SQL Server - how to dynamically determine financial year?

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?

Solution

  • 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").