Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Error ora-1840 when using multiple comparison with date fields as part of the where clause in SELECT command

ORA-01840: input value not long enough for date format

Sample code that illustrate this:

SELECT COUNT(*) FROM tab WHERE col2 >= TODAY - 30 AND col2 < TODAY
AND col1 = 2.

CAUSE:

Bug# 20040805-005

CAUSE:

There is a missing TO_DATE in the SELECT generated by DataServer.
By using -Dsrv qt_debug,SQL, for the select as:

SELECT COUNT(*) FROM test WHERE col2 >= TODAY - 30 AND col2 < TODAY
AND col1 = 2.

The SQL generated is

SELECT ( COUNT (*)) FROM USER1.TEST T0 WHERE (COL2 >= TO_DATE(:1,
'YYYYMMDDHH24MISS') AND COL2 < :2 AND COL1 = :3)

But changing the order of the criteria using:

SELECT COUNT(*) FROM test WHERE col2 < TODAY AND col2 >= TODAY - 30
AND col1 = 2.

The SQL generated is:

SELECT ( COUNT (*)) FROM USER1.TEST T0 WHERE (COL2 < TO_DATE(:1,
'YYYYMMDDHH24MISS') AND COL2 >= TO_DATE(:2,'YYYYMMDDHH24MISS') AND COL1 = :3)

FIX:

As a workaround, change the order of the criteria

So, instead of use:

SELECT COUNT(*) FROM tab
WHERE col2 >= TODAY - 30
AND col2 < TODAY
AND col1 = 2.

Use:

SELECT COUNT(*) FROM tab
WHERE col2 < TODAY
AND col2 >= TODAY - 30
AND col1 = 2.