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

February 17, 2022

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

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

>Solution :

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