Oracle SQL to_date& to_timestamp ORA-01858:在期望数字&的地方发现了非数字字符ORA-01850:小时必须在0到23之间 [英] 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

问题描述

我有一些代码:

代码

SELECT to_date(it.DSTAMP, 'DD/MM/YYYY') AS "Date", to_timestamp(it.DSTAMP, 'HH24:MI:SS') AS Time
FROM itable it

错误

ORA-01858:在需要数字的地方找到了非数字字符 01858.00000-在期望数字的位置发现了非数字字符" *原因:使用日期格式模型转换的输入数据为 不正确.输入数据不包含数字，其中数字为 格式模型要求. *操作:修正输入数据或日期格式模型，以确保 元素在数量和类型上匹配.然后重试该操作.

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.

如果我删除to_date

ORA-01850:小时必须在0到23之间 01850.00000-小时必须在0到23之间" *原因:
*动作:

ORA-01850: hour must be between 0 and 23 01850. 00000 - "hour must be between 0 and 23" *Cause:
*Action:

DSTAMP字段返回24-SEP-14 08.55.33.997545000，不带任何格式.

The DSTAMP field returns 24-SEP-14 08.55.33.997545000 without any formatting.

显然预期的输出是

2014年9月24日& 08:55:34

24/09/2014 & 08:55:34

推荐答案

似乎.DSTAMP是TIMESTAMP

It seems that it.DSTAMP is a TIMESTAMP

将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

这篇关于Oracle SQL to_date& to_timestamp ORA-01858:在期望数字&的地方发现了非数字字符ORA-01850:小时必须在0到23之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！