Zone SQL Error When Selecting A Field With TO_DATE In The Where Clause (Doc ID 2511464.1)

Last updated on DECEMBER 03, 2019

Applies to:

Goal

On : 2.3.0.1.0 version, Database

Zone SQL error when selecting a field with TO_DATE in the where clause

currently using the M1_TASK_CHAR table to store a custom field with a date value (Date Required). you have customized the Activity Search zones to search for activities with a Date Required field equal to the input date.

It was working before but now got an error: ORA-01861: literal does not match format string

When running a simple SQL statement using SQL developer, this SQL statement works:
SELECT * FROM M1_TASK_CHAR WHERE CHAR_TYPE_CD='CM-DTREQ' AND TO_DATE(SRCH_CHAR_VAL,'YYYY-MM-DD') >= TO_DATE('2018-12-01','YYYY-MM-DD');
However, when select specified field (not *), we get the same error:
SELECT TASK_ID FROM M1_TASK_CHAR WHERE CHAR_TYPE_CD='CM-DTREQ' AND TO_DATE(SRCH_CHAR_VAL,'YYYY-MM-DD') >= TO_DATE('2018-12-01','YYYY-MM-DD');

ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
  the format string (with the exception of leading whitespace). If the
  "FX" modifier has been toggled on, the literal must match exactly,
  with no extra whitespace.
*Action: Correct the format string to match the literal.

There is currently no existing base functionality that stores a date information in the M1_TASK_CHAR table or any other similar tables.
However, this can easily be replicated by created a test data in the M1_TASK_CHAR table and run the select statement.
 

Solution

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