ORA-01722 oracle 中的无效号码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28917707/
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-01722 INVALID NUMBER in oracle
提问by Devendra S
I am getting invalid number error message while executing the below select statement.Can any one have an idea about the issue..Please let me know.
我在执行以下选择语句时收到无效号码错误消息。任何人都可以了解这个问题。请告诉我。
select TO_DATE(TO_CHAR('2015/01/22 00:00:00','YYYY/MM/DD'),'YYYY/MM/DD')
actually i want oracle standard date format without time stamp for this date '2015/01/22 00:00:00'
实际上我想要这个日期'2015/01/22 00:00:00'的没有时间戳的oracle标准日期格式
回答by Brian DeMilia
select to_date('2015/01/22 00:00:00','YYYY/MM/DD HH24:MI:SS') as dt
from dual
Fiddle - http://sqlfiddle.com/#!4/6a3a6/1/0
小提琴 - http://sqlfiddle.com/#!4/6a3a6/1/0
As an FYI, the Oracle DATEdata type does include the time component (just not down to fractional seconds, as is the case with the TIMESTAMPdata type).
作为仅供参考，OracleDATE数据类型确实包括时间组件（只是不是小数秒，就像TIMESTAMP数据类型的情况一样）。
If you are converting values and want to bring all the time values to zero you can use the truncfunction like this (which changes 12:07:00 to 00:00:00):
如果您正在转换值并希望将所有时间值归零，您可以使用这样的trunc函数（将 12:07:00 更改为 00:00:00）：
select trunc(to_date('2015/01/22 12:07:00','YYYY/MM/DD HH24:MI:SS'),'DD') as dt_with_time_zerod
from dual
Fiddle - http://sqlfiddle.com/#!4/6a3a6/2/0
小提琴 - http://sqlfiddle.com/#!4/6a3a6/2/0
If the source is itself a date and you want to convert the date to a string in the Oracle default date format ('DD-MON-RR') you can achieve that by running:
如果源本身是一个日期，并且您想将日期转换为 Oracle 默认日期格式 ('DD-MON-RR') 的字符串，则可以通过运行来实现：
select to_char(trunc(to_date('2015/01/22 12:07:00','YYYY/MM/DD HH24:MI:SS'),'DD'),'DD-MON-RR') as dt_with_time_zerod
from dual
Fiddle - http://sqlfiddle.com/#!4/6a3a6/3/0
回答by David Faber
actually i want oracle standard date format without time stamp for this date '2015/01/22 00:00:00'
实际上我想要这个日期'2015/01/22 00:00:00'的没有时间戳的oracle标准日期格式
I'm not sure what you mean by "Oracle standard date format." The format in which a date would appear would be based on your NLS settings (in particular, NLS_DATE_FORMAT). If you are just trying to format this string representing a date, then you might want something like the following:
我不确定您所说的“Oracle 标准日期格式”是什么意思。日期显示的格式将基于您的 NLS 设置（特别是NLS_DATE_FORMAT）。如果您只是想格式化这个表示日期的字符串，那么您可能需要如下内容：
SELECT TO_CHAR(TO_DATE('2015/01/22 00:00:00','YYYY/MM/DD HH:MI:SS'), 'YYYY/MM/DD')
FROM dual;
That is, you have the TO_CHAR()and TO_DATE()functions in the wrong order, and an incomplete date mask for the call to TO_DATE().
也就是说，您的TO_CHAR()和TO_DATE()函数顺序错误，并且调用 的日期掩码不完整TO_DATE()。
回答by Jon Heller
Try using date literals with the standard ISO 8601 format.
尝试使用具有标准ISO 8601 格式的日期文字。
date '2015-01-22'
回答by toha
I suggest you not to give hour-minute-second if you do not want to show the time.
如果你不想显示时间，我建议你不要给出小时-分-秒。
This is my simplest answer :
这是我最简单的答案：
SELECT TO_DATE('2015/01/22','YYYY/MM/DD') FROM dual
回答by Dan Bracuk
If it's a date field, to_char without a mask will give you what you say you want.
如果它是一个日期字段，没有掩码的 to_char 会给你你想要的东西。
