Date Range - Issues retrieving values in date range when date is saved as a varchar formatted: DD-MON-YY

In a table I have no control over, I have to fetch values within a date range. The date is saved as a varchar in an odd format of dd-mon-yy e.g. 01-Jan-24.

I have tried combinations of TRUNC, TO_DATE, TO_CHAR, but have not had luck. Any assistance would be greatly appreciated. For example, currently,

BETWEEN TO_DATE(‘02-Oct-23’, 'dd-mon-rr') AND TO_DATE(‘14-Jan-24’, 'dd-mon-rr’) 

is sending results from January of 2023. I'm sure this is a relatively simple gap in my knowledge, but I haven't been able to find a simple solution, so here we are. Will respond, if I find the solution.

If you want to find values from the table within a date range then convert your column values from VARCHAR2 to a DATE using TO_DATE on the left-hand side of the BETWEEN filter:

SELECT *
FROM   table_name
WHERE  TO_DATE(date_string, 'DD-MON-RR', 'NLS_DATE_LANGUAGE=English')
       BETWEEN DATE '2023-10-02' AND DATE '2024-01-14'

(Note: you can use date literals on the right-hand side of the filter.)

Which, for the sample data:

CREATE TABLE table_name (date_string VARCHAR2(9));

INSERT INTO table_name (date_string)
SELECT '14-Jan-23' FROM DUAL UNION ALL
SELECT '01-Oct-23' FROM DUAL UNION ALL
SELECT '02-Oct-23' FROM DUAL UNION ALL
SELECT '31-Dec-23' FROM DUAL UNION ALL
SELECT '01-Jan-24' FROM DUAL UNION ALL
SELECT '14-Jan-24' FROM DUAL UNION ALL
SELECT '15-Jan-24' FROM DUAL;

Outputs:

fiddle