to_char and to_date function in oracle

Source : http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY”. Therefore, when you issue the query
select b from x;

you will see something like:
B
———
01-APR-98

Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format. For example,
SELECT TO_CHAR(b, ‘YYYY/MM/DD’) AS b
FROM x;

returns the result:
B
—————————————————————————
1998/04/01
The general usage of TO_CHAR is:
TO_CHAR(, ”)

where the  string can be formed from over 40 options. Some of the more popular ones include:
  , for example.

MM Numeric month (e.g.07)
MON Abbreviated month name (e.g.JUL)
MONTH Full month name (e.g.JULY)
DD Day of month (e.g.24)
DY Abbreviated name of day (e.g.FRI)
YYYY 4-digit year (e.g.1998)
YY Last 2 digits of the year (e.g.98)
RR Like YY, but the two digits are “rounded” to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM) Meridian indicator
HH Hour of day (112)
HH24 Hour of day (023)
MI Minute (059)
SS Second (059)

Leave a Reply