TO_DATE ('01/01/1980','dd/mm/yyyy') is not working when calling oracle database from Java

Question

In SQL Navigator(Oracle) the following three queries are working

SELECT * FROM some_table a where TO_DATE (A.CREATE_DATE, 'dd/mm/yyyy') BETWEEN TO_DATE ('01/01/1980','mm/dd/yyyy') AND TO_DATE ('01/01/2021', 'mm/dd/yyyy') AND rownum<5   
SELECT * FROM some_table a where TO_DATE (A.CREATE_DATE, 'dd/mm/yyyy') BETWEEN TO_DATE ('01/01/1980','dd/mm/yyyy') AND TO_DATE ('01/01/2021', 'dd/mm/yyyy') AND rownum<5   
SELECT * FROM some_table a WHERE TO_DATE(A.CREATE_DATE,'dd-MON-yyyy') BETWEEN TO_DATE ('01-JAN-1980','dd-MON-yyyy') AND TO_DATE ('01-JAN-2021','dd-MON-yyyy') AND rownum<5
SELECT * FROM some_table a where TO_DATE (A.CREATE_DATE, 'DD/MM/YYYY') BETWEEN TO_DATE ('01/01/1980','DD/MM/YYYY') AND TO_DATE ('01/01/2021', 'DD/MM/YYYY') AND rownum<5

In Oracle DB the date is in this format '15-Sep-2020 10:22:23'

But when run the code from Java using ojdbc7-12.1.0.2.jar driver, I am not getting any results

when I remove where condition (SELECT * FROM acadmin.ctmgmt_call_info a where rownum<5). I am getting results. For testing, I have hard-coded the dates.

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con = DriverManager.getConnection("url","username", "pwd");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next())
    {
        System.out.println(rs.getString(1));
    }
        

How to make it work with date where condition?

Answer 1

I was able to fix the issue by replacing TO_DATE (A.CREATE_DATE, 'dd/mm/yyyy') with A.CREATE_DATE

SELECT * FROM some_table A where A.CREATE_DATE BETWEEN TO_DATE ('01/01/1980','dd/mm/yyyy') AND TO_DATE ('01/01/2021', 'dd/mm/yyyy') AND rownum<5