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

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

CodePudding user response：

The problem is that your case returns text in the when clauses and a date in the else. Try this:

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 to_char(TRAVEL_DT)
       END AS FISCAL_YEAR
FROM TRAVEL_DATA

CodePudding user response：

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

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