Beginning of financial year in Oracle

In Australia, the financial year runs from 1 July to 30 June. When doing financial reports, sometimes you need to know the financial year current at the time. Oracle (amazingly) makes it easy...


select
case
when mth < 4 then trunc(add_months(dt,-6),'Q') when mth < 7 then trunc(add_months(dt,-9),'Q') when mth < 10 then trunc(dt,'Q') else trunc(add_months(dt,-3),'Q') end fin_year /*return the beginning of the current financial year*/ from (select dt, to_number(to_char(dt ,'MM')) mth from ( select sysdate dt from dual) xx ) xx1