Oracle 21c

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

CREATE TABLE travel_data (lst_name, frst_nm, travel_dt) AS
SELECT 'Abbot', 'Alice', DATE '2020-09-30' FROM DUAL UNION ALL
SELECT 'Baron', 'Betty', DATE '2020-10-01' FROM DUAL UNION ALL
SELECT 'Curry', 'Carol', DATE '2019-10-01' FROM DUAL UNION ALL
SELECT 'Doyle', 'Doris', DATE '2021-09-30' FROM DUAL UNION ALL
SELECT 'Eagle', 'Emily', DATE '2021-10-01' FROM DUAL
5 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Select Lst_name,
       frst_nm,
       travel_dt,
       'FY-' || EXTRACT(YEAR FROM ADD_MONTHS(travel_dt, -9)) AS fiscal_year,
       ADD_MONTHS(TRUNC(ADD_MONTHS(travel_dt,-9),'YY'),9) AS start_of_fiscal_year
FROM TRAVEL_DATA


LST_NAME
FRST_NM
TRAVEL_DT
FISCAL_YEAR
START_OF_FISCAL_YEAR


Abbot
Alice
2020-09-30 00:00:00
FY-2019
2019-10-01 00:00:00

Baron
Betty
2020-10-01 00:00:00
FY-2020
2020-10-01 00:00:00

Curry
Carol
2019-10-01 00:00:00
FY-2019
2019-10-01 00:00:00

Doyle
Doris
2021-09-30 00:00:00
FY-2020
2020-10-01 00:00:00

Eagle
Emily
2021-10-01 00:00:00
FY-2021
2021-10-01 00:00:00


Select Lst_name,
       frst_nm,
       travel_dt,
       CASE
       WHEN TRAVEL_DT <  DATE '2020-10-01' THEN 'FY-2019'
       WHEN TRAVEL_DT >= DATE '2020-10-01' THEN 'FY-2020'
       END AS FISCAL_YEAR
FROM   TRAVEL_DATA


LST_NAME
FRST_NM
TRAVEL_DT
FISCAL_YEAR


Abbot
Alice
2020-09-30 00:00:00
FY-2019

Baron
Betty
2020-10-01 00:00:00
FY-2020

Curry
Carol
2019-10-01 00:00:00
FY-2019

Doyle
Doris
2021-09-30 00:00:00
FY-2020

Eagle
Emily
2021-10-01 00:00:00
FY-2020

LST_NAME	FRST_NM	TRAVEL_DT	FISCAL_YEAR	START_OF_FISCAL_YEAR
Abbot	Alice	2020-09-30 00:00:00	FY-2019	2019-10-01 00:00:00
Baron	Betty	2020-10-01 00:00:00	FY-2020	2020-10-01 00:00:00
Curry	Carol	2019-10-01 00:00:00	FY-2019	2019-10-01 00:00:00
Doyle	Doris	2021-09-30 00:00:00	FY-2020	2020-10-01 00:00:00
Eagle	Emily	2021-10-01 00:00:00	FY-2021	2021-10-01 00:00:00