oracle to_char 函数问题，日期以“dd-mon-yyyy”格式传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14136487/
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
to_char function issue with date passing in the format of 'dd-mon-yyyy'
提问by Taniya
My query is
我的查询是
select TO_CHAR('03-JAN-2013', 'D') from dual;
select TO_CHAR('03-JAN-2013', 'D') from dual;
but an error occured as
但发生了错误
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
But when query changed as select TO_CHAR(sysdate, 'D') from dual;
但是当查询更改为 select TO_CHAR(sysdate, 'D') from dual;
Result is right answer 5.
结果是正确答案 5。
I can't understand why it is behaving like this, please help me.
我不明白为什么它会这样，请帮助我。
Thanks in advance
提前致谢
采纳答案by TechDo
Please cast the string to date before selecting.
请在选择之前将字符串转换为日期。
SELECT TO_CHAR(CAST('03-JAN-2013' AS DATE), 'D') FROM DUAL;
OR
或者
SELECT TO_CHAR(TO_DATE('03-JAN-2013'), 'D') FROM DUAL;
回答by Nick Krasnov
The '03-JAN-2013'string literal must be converted to the datedata type before invoking TO_CHARfunction:
在调用函数之前，'03-JAN-2013'字符串文字必须转换为date数据类型TO_CHAR：
select TO_CHAR(to_date('03-JAN-2013', 'dd-MON-YYYY'), 'D') as res
from dual
RES
-----
5
