Determine fiscal year of a date using Oracle SIGN/DECODE

Have a date/list of dates and need to calculate what fiscal year they lie in?

Using a combination of Oracle provided functions SIGN and DECODE is a quick and simple way!

Basically you use SIGN to compare your date's month to 7 (or the beginning month of your fiscal year) and then use decode to determine whether to add 1 to the dates year or not.

Please find some examples below

Single date

SELECT
EXTRACT(YEAR FROM A.YOUR_DATE) +  DECODE(SIGN(TO_CHAR(A.YOUR_DATE, 'MM') - 7), -1, 0, 1) AS FISCAL_YEAR
FROM (SELECT DATE '2012-12-01' as YOUR_DATE FROM DUAL) A

Mimicking list of dates

SELECT 
A.RANDOM_DATE, 
EXTRACT(YEAR FROM a.RANDOM_DATE) + DECODE(SIGN(TO_CHAR(a.RANDOM_DATE,'MM') - 7), -1, 0, 1) AS FISCAL_YEAR
FROM (SELECT ADD_MONTHS(trunc(sysdate), ROUND(DBMS_RANDOM.VALUE(-55,55),0)) AS RANDOM_DATE FROM DUAL
CONNECT BY LEVEL <= 10) a