Oracle PL/SQL - Date format in PL/SQL

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. DATE data type in PL/SQL

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--  ww w  .  j a v  a  2  s.co  m
  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