时间戳

首页 > 解决方案 > 从 Java 调用 oracle 数据库时，TO_DATE ('01/01/1980','dd/mm/yyyy') 不起作用

date - 从 Java 调用 oracle 数据库时，TO_DATE ('01/01/1980','dd/mm/yyyy') 不起作用

问题描述

在 SQL Navigator(Oracle) 中，以下三个查询正在工作

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

在 Oracle DB 中，日期格式为 '15-Sep-2020 10:22:23'

但是当使用 ojdbc7-12.1.0.2.jar 驱动程序从 Java 运行代码时，我没有得到任何结果

当我删除 where 条件时（SELECT * FROM acadmin.ctmgmt_call_info a where rownum<5）。我得到了结果。为了测试，我对日期进行了硬编码。

    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));
    }

如何使其适用于日期条件？

标签： datejdbcojdbcjdbc-odbc

解决方案

我能够通过用 A.CREATE_DATE 替换 TO_DATE (A.CREATE_DATE, 'dd/mm/yyyy') 来解决这个问题

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

推荐阅读