Introduction

To display the date in a meaningful way, you need to use the TO_CHAR function:

my_val := to_char(DATE variable [,format mask])

TO_CHAR converts the date value into the string by using a specified format mask.

The following table shows the most commonly used formatting options.

Format
Meaning
Y, YY, YYYY, YYYY
Year (from one to 4 digits)
RR, RRRR
Rounded year (accepts 2- or 4-digit values and converts 2-digit values into the appropriate century)
MM, MONTH, MON
Month of the year, name of the month, abbreviated name
W, WW


Week of the month, week of the year (not a calendar week,
because the first week starts at the 1st of the month/year
and ends at the 7th)
D, DD, DDDD
Day of the week, day of the month, day of the year
DAY, DY
Name of day (fixed length: 9 char), abbreviated name of day
HH, HH24
Hour of the day (1-12), hour of the day (0-23)
A.M./ AM
Meridian indicator with or without periods
MI
Minutes (0-59)
SS, SSSSS
Seconds (0-59), seconds from midnight (0-86399)

You can use some characters as separators:comma, dot, space, and semicolon within the format mask.

Everything else has to be enclosed in double quotes.

Demo

SQL>
SQL> declare-- from w ww .j a v  a 2  s.  c om
  2       v_dt DATE :=sysdate;
  3       v_tx VARCHAR2(2000);
  4  begin
  5      v_tx:=to_char(v_dt,'"Today is": DAY');
  6      DBMS_OUTPUT.put_line(v_tx);
  7  end;
  8  /
Today is: SATURDAY

PL/SQL procedure successfully completed.
SQL>

Related Topic