All geek questions in one place

Oracle's strange behavior: TO_DATE and CONCAT in the WHERE clause

I have a somewhat complicated SQL query that is pulled from the invoice table. The requested table uses three numeric fields to create the date (one for the day, month, and year).

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

So what is the difference between using concat and TO_DATE in a where clause?

For example, this next part of WHERE causes a 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 select statement evaluates perfectly

to_date(concat(invoice_year,concat('-',concat(invoice_month, concat('-',invoice_day)))),'YYYY-MM-DD')as invoice_date
+3
1

, TO_DATE; WHERE.

SELECT, , WHERE.

, , , .

, NULL , .

+6

Source: https://habr.com/ru/post/1795915/

More articles:


All Articles