ORA-01878 Select Query Fails When Date Range During DST Time Change (Doc ID 2780633.1)

Last updated on MARCH 12, 2024

Applies to:

Symptoms

Oracle JDBC driver fails with java.sql.SQLDataException: ORA-01862: the numeric value does not match the length of the format item

when a SQL query spans between the Day light saving Time of 2021 and the table column is TIMESTAMP  WITH LOCAL TIME ZONE

Example

select * from test_jdbc1 where created > '14-MAR-21 01.29.11.973000 AM' AND
created < '14-MAR-21 2.29.11.973000 AM'

The issue was reported with 18.10

java.sql.SQLDataException: ORA-01878: specified field not found in datetime
or interval

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
        at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)

        at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatemen
t.java:928)
        at
oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStateme
nt.java:1158)
        at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1
093)
        at
oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)

        at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1
285)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedState
ment.java:3735)
        at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatemen
t.java:3847)
        at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedS
tatementWrapper.java:1098)
        at JDBCInfo.main(JDBCInfo.java:72)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.