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