Search code examples
sqloracle-databasedateoracle10gora-01858

Strange Oracle behavior: TO_DATE and CONCAT in WHERE clause

I have a somewhat complex SQL query that is pulling from a table of invoices. The table being queried uses three number fields to create a date (one for day, month, and year).

This query uses a combination of concat function calls inside of a TO_DATE function. In the SELECT part of the query there is no problem, but when I use an identical statement in the WHERE clause, I get a "ORA-01858: a non-numeric character was found where a numeric was expected" that refers to the first use of concat inside of TO_DATE in the WHERE clause.

So, what is different about using concat and TO_DATE in a where clause?

For example, this following part of the WHERE causes the non-numeric character error

to_date(
  concat(invoice_year,
  concat('-',
  concat(invoice_month, 
  concat('-',invoice_day)))),'YYYY-MM-DD')
  > add_months(sysdate,-6)

but this statement in the select evaluates just fine 

to_date(
  concat(invoice_year,
  concat('-',
  concat(invoice_month,
  concat('-',invoice_day)))),'YYYY-MM-DD') as invoice_date
Solution

  • There may be rows in the table that will cause an error when the TO_DATE expression is evaluated; but that do not meet the other conditions in the WHERE clause.

    When you put the expression in the SELECT list, it is not evaluated until a row has passed all conditions in the WHERE clause.

    When you use the expression in a condition, it may be evaluated before the other conditions, so it might now be evaluated for rows where it causes the error.

    Note that if you have NULLs in any of those fields for a given row, it would cause the error.