Is there a way to CASE DD-MMM-YYYY date format?

Question

I am trying to case a column that has a date format of DD-MMM-YYYY.

Basically, I am trying to convert the dates into a Fiscal Year column to better analyze work data. I can't share the actual data here due to privacy issues. Below is my syntax.

I get the ORA-00932: inconsistent datatypes: expected DATE got CHAR.

The TRAVEL_DT column is a DATE data type.

Select Lst_name, frst_nm, travel_date,
       case when TRAVEL_DT <= '30-SEP-2020' THEN 'FY-2019'
            WHEN TRAVEL_DT >= '01-OCT-2020' THEN 'FY-2020'
            ELSE TRAVEL_DT
       END AS FISCAL_YEAR
FROM TRAVEL_DATA

Answer 1

To get the date at the start of the fiscal year you can:

• Subtract 9 months;
• truncate to the start of the year; and then
• add 9 months again

If you just want to know the fiscal year then:

• Subtract 9 months;
• extract the year.

Like this:

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

Which, for the sample data:

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

Outputs:

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

---

If you just want to fix your code then you can use date literals:

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

Note: You do not need the ELSE condition as it will only ever be matched when TRAVEL_DT is NULL.

db<>fiddle here