Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
7 rows affected

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'


DATE_STRING


02-Oct-23

31-Dec-23

01-Jan-24

14-Jan-24