Oracle SQL to_date & to_timestamp ORA-01858：在需要数字的位置找到非数字字符 & ORA-01850：小时必须介于 0 和 23 之间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27425515/
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 SQL to_date & to_timestamp ORA-01858: a non-numeric character was found where a numeric was expected & ORA-01850: hour must be between 0 and 23
提问by Matt
I have a small bit of code:
我有一小段代码：
Code
代码
SELECT to_date(it.DSTAMP, 'DD/MM/YYYY') AS "Date", to_timestamp(it.DSTAMP, 'HH24:MI:SS') AS Time
FROM itable it
Errors
错误
ORA-01858: a non-numeric character was found where a numeric was expected 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.
ORA-01858: 在需要数字的地方找到了非数字字符 01858. 00000 - “在需要数字的地方找到了非数字字符” *原因：使用日期格式模型转换的输入数据不正确. 输入数据不包含数字，其中格式模型需要数字。*操作：修复输入数据或日期格式模型以确保元素在数量和类型上匹配。然后重试该操作。
Error if I remove to_date
如果我删除错误 to_date
ORA-01850: hour must be between 0 and 23 01850. 00000 - "hour must be between 0 and 23" *Cause:
*Action:
ORA-01850：小时必须在 0 到 23 01850 之间。00000 - “小时必须在 0 到 23 之间”*原因：
*操作：
The DSTAMPfield returns 24-SEP-14 08.55.33.997545000without any formatting.
该DSTAMP字段24-SEP-14 08.55.33.997545000不带任何格式返回。
Obviously expected output is
显然预期输出是
24/09/2014 & 08:55:34
24/09/2014 & 08:55:34
回答by Multisync
It seems that it.DSTAMP is a TIMESTAMP
看来 it.DSTAMP 是一个 TIMESTAMP
Replace to_dateand to_timestampwith to_char
替换to_date并to_timestamp使用to_char
SELECT to_char(it.DSTAMP, 'DD/MM/YYYY') AS "Date", to_char(it.DSTAMP, 'HH24:MI:SS') AS Time
FROM itable it
