ORA-01858: a non-numeric character was found where a numeric was expected

Question

I have a function that takes in a collection of SearchCriteria Objects: columnName or Key, operator (<, <=, like, etc), and value.

The function builds up a Command Object. I made the value a command parameter and now my Unit Tests will not work for Dates. But all of my Unit Tests work against all other datatypes like varchar.

In the debugger, one of my date unit tests that fail end up with the cmd looking like this:

SELECT * FROM (SELECT DocumentId 
               FROM idx1_AuthLetters a 
               INNER JOIN Documents b ON a.DocumentId = b.Id
               WHERE Status in ('L','S','V')  AND  letter_date <= :1 
               ORDER BY DOCUMENTID ) 
WHERE RowNum <= 14 

I did have the parameter named like :letter_date. But I might have :letter_date >= ### && :letter_date <= ### where I am looking between two dates. I cannot have the same parameter name twice so I use an i++ counter as parameter name while I am looping through all of my SearchCriteria Objects. Odd to see a parameter named like this I know but it is working for the most part.

If I take this and put in my Query Window, and look inspect the param value and plug that in:

SELECT * FROM (SELECT DocumentId 
               FROM idx1_AuthLetters a 
               INNER JOIN Documents b ON a.DocumentId = b.Id 
               WHERE Status in ('L','S','V')  AND  
                     letter_date <= TO_DATE('2013-1-21', 'yyyy-mm-dd') 
               ORDER BY DOCUMENTID ) 
WHERE RowNum <= 14 

it works fine. But it will not work from the C# code from my Unit Test. Again this works for all other data types. And it use to work before I parameterized the value in the select statement.

Exact error is:

{"ORA-01858: a non-numeric character was found where a numeric was expected"}

Answer 1

The answer is really simple here: In your command, build the following string TO_DATE(:1, 'yyyy-mm-dd') . Then, just make sure that string that goes into :1 has accurate format.

Your command text should be this:

SELECT * FROM (SELECT DocumentId 
           FROM idx1_AuthLetters a 
           INNER JOIN Documents b ON a.DocumentId = b.Id
           WHERE Status in ('L','S','V')  AND  letter_date <= TO_DATE(:1, 'yyyy-mm-dd')  
           ORDER BY DOCUMENTID ) 
WHERE RowNum <= 14