Working with dates in any database can cause some frustration. How do the TO_CHAR and TO_DATE functions help?

Question

Working with dates in any database can cause some frustration. How do the TO_CHAR and TO_DATE functions help in working with date logic within Oracle?

Answer 1

TO_CHAR can cast a DATE column to a human-readable string representing a date. TO_DATE can cast a human-readable string to a DATE type.

Answer 2

TO_CHAR can be used to change the format of a date or number into a character value based on the format. For example (in oracle),
select to_char(sysdate, 'yyyy/mm/dd') from dual;
select to_char(myVal,'9999.99') from mytable;

The TO_DATE function reverses this for dates. For example, to take a date literal of 12/31/1999 and store it into a date datatype column, you'd use
update mytable set mydate=to_date('12/31/1999','mm/dd/yyyy') where...