"Lookup Error - Oracle Database Error: ORA-01843: not a valid month" when running a query that has an IN clause with specific dates (4311311)

Chat now with support

Return

Did this article solve an issue for you?

[Select Rating]

Title

"Lookup Error - Oracle Database Error: ORA-01843: not a valid month" when running a query that has an IN clause with specific dates
Description
In Query Builder
1. select a date column.
2. Add an IN Clause as Constant and select a couple of date values from the dropdown.
3. The query generated looks like this:
SELECT "TABLENAME".HIRE_DATE
FROM SCHEMA."TABLENAME"
WHERE "TABLENAME".HIRE_DATE IN (
('1/3/1990 12:00:00 AM'),('9/21/1989 12:00:00 AM'));
This results in error:
"Lookup Error - Oracle Database Error: ORA-01843: not a valid month"
Version 5.1.3 uses TO_DATE function and works correctly
Resolution

WORKAROUND
Send the Query to the Editor and amend the query to use the TO_DATE function e.g.
TO_DATE ('1/3/1990 12:00:00', 'yyyy/mm/dd hh24:mi:ss')
STATUS
Waiting for fix in a future release of Toad Data Point
Defect ID

TMB-3834

Did this article solve an issue for you?

[Select Rating]

Title