Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

Oracle training Weblogic Book
Easy Oracle Jumpstart
Oracle training & performance tuning books
Burleson Consulting Remote DB Administration
Reply to this topicStart new topic
> to char vs to_date, I searched lot of blogs not clearly understand to_char,to_date func
post Mar 26 2012, 01:39 PM
Post #1

Advanced Member

Group: Members
Posts: 78
Joined: 16-March 12
From: Chennai from India
Member No.: 46,939

Hai to everyone ,
I am very new to ORACLE. Please clear my question.Really i spent more than 4 hours cant understand.

SQL> select to_date('01-jan-04','DD/MONTH/YY') from dual;


SQL> select to_date('01-jan-04','DD/MONTH/YEAR') from dual;
select to_date('01-jan-04','DD/MONTH/YEAR') from dual
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

select to_char(systdate) from dual;
select to_date(sysdate) from dual;

both showing same output. no difference

both function what actually does in SQL??
Go to the top of the page
+Quote Post
post Mar 26 2012, 02:10 PM
Post #2

Advanced Member

Group: Members
Posts: 12,673
Joined: 26-January 04
Member No.: 13


First, use the oerr utility on your server to look-up your ORA-01820 error:

ORA-01820:format code cannot appear in date input formatCause:A date specification contained an invalid format code. Only the following may be specified when entering a date: year, month, day, hours, minutes, seconds, Julian day, A.M./P.M. and B.C./A.D.Action:Remove the invalid format code from the date specification.


As you see, this is an invalid data format vode:

select to_date('01-jan-04','DD/MONTH/YEAR') from dual

See here for the valid date display codes:


In your case, I assume that you want DD/MON/YYYY


Remember, your nls_date_format determines hoiw a DATE datatypes is displayed, and you can change it easily using the to_char function to whataver date format you like:

Please read:


Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:


Lo-Fi Version Time is now: 1st December 2015 - 04:12 AM