C# ORA-01858: 在需要数字的地方发现了非数字字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17091774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01858: a non-numeric character was found where a numeric was expected
提问by Sam
I have a function that takes in a collection of SearchCriteriaObjects:
columnName or Key, operator (<, <=, like, etc), and value.
我有一个函数，它的集合SearchCriteria对象：列名或关键，运营商（<，<=，like等），和价值。
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.
该函数建立一个命令对象。我将该值设为命令参数，现在我的单元测试不适用于日期。但是我所有的单元测试都适用于所有其他数据类型，如 varchar。
In the debugger, one of my date unit tests that fail end up with the cmd looking like this:
在调试器中，我的一个失败的日期单元测试最终导致 cmd 如下所示：
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.
我确实有类似 :letter_date 的参数。但我可能有 :letter_date >= ### && :letter_date <= ### 我在两个日期之间查找。我不能有两次相同的参数名称，因此我在遍历所有 SearchCriteria 对象时使用 i++ 计数器作为参数名称。奇怪的是看到一个这样命名的参数，我知道但它在大多数情况下都有效。
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.
它工作正常。但是它不能从我的单元测试中的 C# 代码中工作。这同样适用于所有其他数据类型。它用于在我参数化 select 语句中的值之前工作。
Exact error is:
确切的错误是：
{"ORA-01858: a non-numeric character was found where a numeric was expected"}
{“ORA-01858：在需要数字的地方发现了一个非数字字符”}
回答by T.S.
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 :1has accurate format.
答案在这里非常简单：在您的命令中，构建以下字符串TO_DATE(:1, 'yyyy-mm-dd')。然后，只需确保进入的字符串:1具有准确的格式。
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
