oracle 出现错误 - ORA-01858: 在需要数字的地方发现了非数字字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29852389/
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
Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected
提问by Pradeep Choubey
I am getting the error in the below sql:
我在下面的sql中收到错误：
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: 在需要数字的地方发现了非数字字符
SELECT c.contract_num,
CASE
WHEN ( MAX (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) < 32
THEN
'Monthly'
WHEN ( MAX (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) >= 32
AND ( MAX (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) < 91
THEN
'Quarterley'
ELSE
'Yearly'
END
FROM ps_ca_bp_events c
GROUP BY c.contract_num;
回答by Boneist
The error you are getting is either because you are doing TO_DATEon a column that's already a date, and you're using a format mask that is different to your nls_date_formatparameter[1] or because the event_occurrence column contains data that isn't a number.
您收到的错误要么是因为您正在TO_DATE处理一个已经是日期的列，并且您使用的格式掩码与您的nls_date_format参数 [1]不同，要么是因为 event_occurrence 列包含的数据不是数字。
You need to a) correct your query so that it's not using TO_DATE on the date column, and b) correct your data, if event_occurrence is supposed to be just numbers.
您需要 a) 更正您的查询，使其不在日期列上使用 TO_DATE，并且 b) 如果 event_occurrence 应该只是数字，则更正您的数据。
And fix the datatype of that column to make sure you can only store numbers.
并修复该列的数据类型以确保您只能存储数字。
[1] What Oracle does when you do: TO_DATE(date_column, non_default_format_mask)is:
TO_DATE(TO_CHAR(date_column, nls_date_format), non_default_format_mask)
[1]当您执行以下操作时，Oracle 会做什么：TO_DATE(date_column, non_default_format_mask)是：
TO_DATE(TO_CHAR(date_column, nls_date_format), non_default_format_mask)
Generally, the default nls_date_formatparameter is set to dd-MON-yy, so in your query, what is likely to be happening is your date column is converted to a string in the format dd-MON-yy, and you're then turning it back to a date using the format MMDD. The string is not in this format, so you get an error.
通常，默认nls_date_format参数设置为dd-MON-yy，因此在您的查询中，可能发生的情况是您的日期列被转换为格式为 dd-MON-yy 的字符串，然后您使用MMDD 格式。该字符串不是这种格式，因此您会收到错误消息。
回答by Ankur Bhutani
This error can come not only because of the Date conversions
出现此错误的原因不仅是日期转换
This error can come when we try to pass date whereas varchar is expected
or
when we try to pass varchar whereas date is expected.
当我们尝试传递日期而预期 varchar
或
当我们尝试传递 varchar 而预期日期时，可能会出现此错误。
Use to_char(sysdate,'YYYY-MM-DD') when varchar is expected
当需要 varchar 时使用 to_char(sysdate,'YYYY-MM-DD')
回答by karthik G
I added TO_DATEand it resolved issue.
我添加TO_DATE并解决了问题。
Before modification- due to below condition i got this error
修改前- 由于以下条件，我收到此错误
record_update_dt>='05-May-2017'
After modification- after adding to_date, issue got resolved.
修改后 - 添加后to_date，问题得到解决。
record_update_dt>=to_date('05-May-2017','DD-Mon-YYYY')
