Oracle date format problem

I have the following strange problem in Oracle

(Please keep in mind that I have little e开发者_Python百科xperience in SQL and even less in Oracle).

If I do this:

SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI') FROM dual

I get this: 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

Which is the 10th of December '02 which is not correct

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

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.

Don't use TO_DATE() on sysdate; sysdate is already a date.

UPDATE favorite_item  
SET favorite_item.last_used_date = sysdate  
WHERE favorite_item.favorite_item_id = 1`

The problem is using the to_date() function on anything other than a string.

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.

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

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):

select to_date(sysdate, 'DD-MON-YY') from dual;