Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--Generate a range of fiscal years
WITH FISCAL_YEARS AS (
   SELECT 
      CONCAT(SEQUENCE.YEAR, '-07-01')::DATE AS FISCAL_START, 
      CONCAT(SEQUENCE.YEAR + 1, '-06-30')::DATE AS FISCAL_END
   FROM GENERATE_SERIES(2000, 2030) AS SEQUENCE (YEAR)
), 
--Your date range
DATE_RANGE AS (
   SELECT 
      '2021-06-16'::DATE AS RANGE_START, 
      '2022-08-31'::DATE AS RANGE_END
)
SELECT 
  --Case statement in case the range_start is later 
  --than the start of the fiscal year
  CASE 
    WHEN RANGE_START > FISCAL_START 
    THEN RANGE_START 
    ELSE FISCAL_START 
  END AS START_DATE, 
  --Case statement in case the range_end is earlier
  --than the end of the fiscal year
  CASE 
    WHEN RANGE_END < FISCAL_END 
    THEN RANGE_END 
    ELSE FISCAL_END
  END AS END_DATE
FROM FISCAL_YEARS
JOIN DATE_RANGE
  --Join to get all relevant fiscal years 
  ON FISCAL_YEARS.FISCAL_START BETWEEN DATE_RANGE.RANGE_START AND DATE_RANGE.RANGE_END
  OR FISCAL_YEARS.FISCAL_END BETWEEN DATE_RANGE.RANGE_START AND DATE_RANGE.RANGE_END

start_date	end_date
2021-06-16	2021-06-30
2021-07-01	2022-06-30
2022-07-01	2022-08-31

SELECT 3