oracle Oracle日期格式问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4338629/
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 date format problem
提问by the_new_mr
I have the following strange problem in Oracle
我在 Oracle 中有以下奇怪的问题
(Please keep in mind that I have little experience in SQL and even less in Oracle).
（请记住，我在 SQL 方面的经验很少，在 Oracle 方面的经验更少）。
If I do this:
如果我这样做：
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI') FROM dual
I get this: 2010-12-02 18:39
我明白了：2010-12-02 18:39
All fine there.
那里一切都好。
However, if I do this:
但是，如果我这样做：
UPDATE favorite_item
SET favorite_item.last_used_date = TO_DATE(sysdate, 'YYYY-MM-DD HH24:MI')
WHERE favorite_item.favorite_item_id = 1
I get this in my database: 10-DEC-02
我在我的数据库中得到了这个：10-DEC-02
Which is the 10th of December '02 which is not correct
哪个是 02 年 12 月 10 日，这是不正确的
If I do this to confirm:
如果我这样做是为了确认：
SELECT TO_CHAR(favorite_item.last_used_date, 'YYYY-MM-DD HH24:MI') AS last_used_date
FROM favorite_item
WHERE favorite_item.favorite_item_id = 1
I get this: 0002-12-10 00:00
我明白了：0002-12-10 00:00
Which is completely wrong.
这是完全错误的。
What am I doing wrong? I feel that the date setting is not working correctly.
我究竟做错了什么？我觉得日期设置不正常。
Thanks in advance for your help.
在此先感谢您的帮助。
回答by Jim Davis
Don't use TO_DATE()on sysdate; sysdateis already a date.
不要使用TO_DATE()上sysdate; sysdate已经是约会了。
UPDATE favorite_item
SET favorite_item.last_used_date = sysdate
WHERE favorite_item.favorite_item_id = 1`
回答by Rajesh Chamarthi
The problem is using the to_date() function on anything other than a string.
问题是在字符串以外的任何东西上使用 to_date() 函数。
As to why you are getting the wrong results, there is an internal conversion that happens when you use to_date on a date. Since to_date actually takes input as a string, your date is initially converted into a string (according to your NLS_DATE_FORMAT setting) and then converted back to a date. Hence the mismatch.
至于为什么会得到错误的结果，在日期上使用 to_date 时会发生内部转换。由于 to_date 实际上将输入作为字符串，因此您的日期最初会转换为字符串（根据您的 NLS_DATE_FORMAT 设置），然后再转换回日期。因此不匹配。
SQL> select sysdate from dual;
SYSDATE
---------
02-DEC-10
SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;
TO_DATE(S
---------
10-DEC-02
--- This is because, the above string is actually executed as
SQL> select to_date(
to_char('02-DEC-10','YYYY-MM-DD') from dual;
TO_DATE('
---------
10-DEC-02
SQL> select to_date(
2 /* implicit conversion... dd-mon-yy' is my session's NLS_DATE_FORMAT */
3 to_char(sysdate,'dd-mon-yy'),
4 'YYYY-MM-DD')
5 from dual;
TO_DATE(/
---------
10-DEC-02
回答by Paul Abbott
sysdate returns a date, so converting it to a date using to_date(sysdate, ...) is redundant/not necessary. You're getting that odd result because the date is being cast to a string by the to_date function using the Oracle default of "DD-MON-YY" and then back into a date using your supplied format, "YYYY-MM-DD". Since the formats don't match, Oracle is interpreting the year as the day and the day as the year. This works correctly (but, again, is redundant):
sysdate 返回一个日期，因此使用 to_date(sysdate, ...) 将其转换为日期是多余的/不必要的。您得到了那个奇怪的结果，因为 to_date 函数使用 Oracle 默认的“DD-MON-YY”将日期转换为字符串，然后使用您提供的格式“YYYY-MM-DD”返回到日期. 由于格式不匹配，Oracle 将年解释为日，将日解释为年。这可以正常工作（但同样是多余的）：
select to_date(sysdate, 'DD-MON-YY') from dual;
