Oracle date format problem

Question

I have the following strange problem in Oracle

(Please keep in mind that I have little experience 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.

Jim Davis · Accepted Answer · 2015-07-13 00:55:21Z

5

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`

edited Jul 13, 2015 at 0:55

answered Dec 2, 2010 at 19:00

Jim Davis

5,3091 gold badge30 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

the_new_mr Over a year ago

I actually realised this on my home last night. Guess it was my end-of-the day brain :)

Rajesh Chamarthi · Accepted Answer · 2010-12-02 19:16:51Z

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

Paul Abbott · Accepted Answer · 2010-12-02 19:15:26Z

1

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;

edited Dec 2, 2010 at 19:15

answered Dec 2, 2010 at 19:07

Paul Abbott

7,2213 gold badges30 silver badges47 bronze badges

1 Comment

Justin Cave Over a year ago

Your SELECT statement does not generate an error or a completely incorrect result if and only if the current session's NLS_DATE_FORMAT happens to be DD-MON-YY (which is not even the default DD-MON-RR-- this would return the year 2 on a default American English install). But since NLS_DATE_FORMAT depends on the client and the client's regional settings, it's a bad idea to ever rely on any particular NLS_DATE_FORMAT setting.

Collectives™ on Stack Overflow

Oracle date format problem

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related