I've recently migrated into the Oracle RDBMS, I need your help in understanding the differences between to_date and to_char functions in Oracle.
I always confuse myself as to what these functions actually do and when to use them especially when it relates to Date fields in the database.
Greatly appreciate all feedback. Would help me a great deal if an example is provided.
Thanks in advance.
TO_DATE() converts a character string into a date. The syntax is:
select TO_DATE('01/03/2001','DD/MM/YYYY') from dual;
TO_CHAR() converts a date into a character string. The syntax is:
select TO_CHAR(sysdate,'DD/MM/YYYY') from dual;
NOTE: the optional_format_string is optional only if the string_date is in the NLS_DATE_FORMAT for the session.
Be aware of this little trap :
When using a decode for instance to eliminate default values,
make sure the format is oke:
SELECT decode( t.input_date
FROM MyTable t;
When not using a to_date(Null), it may be converted to char
(when it is the first record) and thereby truncate the real dates. When standard representation = mm/dd/yy
a date like 02/15/99 may become 02/15/2099 after this selection.