to char vs to_date, I searched lot of blogs not clearly understand to_char,to_date func Options

[thiyagusham]

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??

[burleson]

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