Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
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
thiyagusham
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;

TO_DATE('
---------
01-JAN-04

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
burleson
post Mar 26 2012, 02:10 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



Hi,


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:


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


See here for the valid date display codes:



http://www.dba-oracle.com/f_to_date.htm



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


http://www.dba-oracle.com/t_ora_01820.htm



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:



http://www.dba-oracle.com/t_nls_date_format.htm


--------------------
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: 25th October 2014 - 05:03 PM