Oracle - ORA-01840: 输入值对于日期格式来说不够长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27569493/
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
Oracle - ORA-01840: input value not long enough for date format
提问by Suganthan Madhavan Pillai
I having an querylike this
我有query这样的
SELECT test_VALUE FROM testTable
WHERE TO_DATE("test", 'yyyy-mm-dd') >= (TO_DATE('2012-01-01', 'yyyy-mm-dd') + '30'
When executing this queryI getting the original data. But when I replacing 30with another inner select querylike this
执行此操作时，query我获取原始数据。但是当我用30另一个inner select query这样的替换时
select test_value from testtable
where TO_DATE("test", 'yyyy-mm-dd') >= (TO_DATE('2012-01-01', 'yyyy-mm-dd') +
(select test_Value1 from testtable where some_condition=1)
I getting the error ORA-01840: input value not long enough for date format
我收到错误 ORA-01840: input value not long enough for date format
The inner query select test_Value1 from testtable where some_condition=1alone will output a result 30
select test_Value1 from testtable where some_condition=1单独的内部查询将输出结果30
回答by Hemardath
I also got the same error. I got the RCA for my issue like one of the records has different values that caused this issue. Make sure that all the data in the column has proper data.
我也遇到了同样的错误。我为我的问题获得了 RCA，就像其中一条记录具有导致此问题的不同值。确保列中的所有数据都有正确的数据。
回答by vapcguy
If your query works when ran directly in say, Oracle SQL Developer, with 30, did you run select test_Value1 from testtable where some_condition=1and get 30? Update test_Value1to 30where some_conditionis equal to 1, so you get 30 back, and re-run the query.
如果您的查询在直接运行时有效，例如 Oracle SQL Developer，使用30，您是否运行select test_Value1 from testtable where some_condition=1并获得30？更新test_Value1到30wheresome_condition等于 1，所以你得到 30，然后重新运行查询。
Seems like you also have a typo - a missing closing parenthesis:
好像你也有一个错字 - 缺少右括号：
>= (TO_DATE('2012-01-01', 'yyyy-mm-dd') +
(select test_Value1 from testtable where some_condition=1))
The error given could happen if the date comes up as an empty string. It's possible not being able to add the value from the final query, because of the missing closing parenthesis, could have made the value empty.
如果日期作为空字符串出现，则可能发生给出的错误。由于缺少右括号，可能无法添加来自最终查询的值，可能会使值变为空。
Also, not sure, but I don't think you want single quotes around 30when adding it directly. You said you got the original list, which makes it sound like it didn't give you items that were 30 days old or older, like it looks like you want.
另外，不确定，但我认为您在30直接添加时不需要单引号。你说你得到了原始列表，这听起来好像它没有给你 30 天或更旧的项目，就像你想要的那样。
回答by mob43059
I got this error when the datatype expected was a datetime and there was NULL in the column instead. The quick fix for me was to filter NULL out from the column with expected datetime values with a simple IS NOT NULL.
当预期的数据类型是日期时间并且列中为 NULL 时，我收到此错误。对我来说，快速修复是使用简单的 IS NOT NULL 从具有预期日期时间值的列中过滤出 NULL。
So you should be sure that your input query is not returning a NULL
所以你应该确保你的输入查询没有返回 NULL
回答by Sanders the Softwarer
You made query with implicit datatype conversions, so you shouldn't be surprised by some strange behavior. It means that your expression produces a result other that DATE and Oracle tries to convert it to date because it required for comparision.
您使用隐式数据类型转换进行查询，因此您不应该对一些奇怪的行为感到惊讶。这意味着您的表达式产生的结果不同于 DATE 和 Oracle 尝试将其转换为日期，因为它需要进行比较。
Be more exact, say,
更准确地说，
select test_value from testtable
where TO_DATE("test", 'yyyy-mm-dd') >= DATE '2012-01-01' +
(select to_number(test_Value1) from testtable where some_condition=1)
and you'll either succeed or got a more descriptive error message. Btw, your SQL means then testtable."test" isn't DATE itself - why?
你要么成功，要么得到更具描述性的错误消息。顺便说一句，你的 SQL 意味着 testtable."test" 不是 DATE 本身 - 为什么？
