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

Either use a proper date literal, or go with the to_date function. — jarlh, Feb 17 '22 at 15:13
@jarlh, how do I do this? my apology, but I am a novice at this. — SQLPhan, Feb 17 '22 at 15:14
Does this answer your question? [oracle convert DD-MON-YY to DD/MM/YYYY](https://stackoverflow.com/questions/40224008/oracle-convert-dd-mon-yy-to-dd-mm-yyyy) — pringi, Feb 17 '22 at 15:16
Another problem is that your case expression has incompatible return data types. 'FY-2019' and 'FY-2020' are character, but TRAVEL_DT is a date. You probably need to do to_char on the date. — jarlh, Feb 17 '22 at 15:17
@jarlh, how do I do a to_char on the date? I am trying to keep the DD-MMM-YYYY format because my internal clients prefer it this way. — SQLPhan, Feb 17 '22 at 15:20
I'd check month and return "FY prior year" if month is less than October, otherwise "FY year". — jarlh, Feb 17 '22 at 15:23
_" I am a novice at this"_ The first thing you need to do is firmly fix in your mind the difference between a date and a string of characters that a human recognizes as representing a date. In reality, a date is just a concept. In oracle, it is properly stored as data type DATE and is a binary structure with no human-recongizable format. Cont . . . — EdStevens, Feb 17 '22 at 16:09
. . . continuing .... What is the data type of your column TRAVEL_DT? If it is not DATE, then you have a serious design flaw. The statement "when TRAVEL_DT <= '30-SEP-2020'", the '30-SEP-2020' (enlcosed in single quotes) is not a date, but a character string, and it will be compared, collated, and sorted as such. So '01-APR-2021' would come before '30-SEP-2020'. — EdStevens, Feb 17 '22 at 16:12

score 1 · Accepted Answer · answered Feb 17 '22 at 15:19

1

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

answered Feb 17 '22 at 15:19

Zakaria

4,715
2
5
31

2

This will fail if the `NLS_DATE_FORMAT` session parameter is not an equivalent of `DD-MON-YYYY` as it relies on implicit string-to-date conversions. It would be better to use date literals. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=f164d94c0ab0f552425f9fc06bbfdb20) – MT0 Feb 17 '22 at 15:50

MT0 · Answer 2 · 2022-02-17T16:02:47.930

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

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

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

2 Answers2