Oracle PL/SQL - DATE data type in PL/SQL


The DATE data type includes all the following information:

  • century,
  • year,
  • month,
  • day,
  • hour,
  • minute, and
  • second.

Valid dates range from January 1, 4712 BC to December 31, AD 9999.

Use the following code to declare the DATE data type:

       variable1_dt DATE;

To take any part of information out of the DATE variable, use the TO_CHAR function with a date format.


SQL> declare-- w w w.j av  a 2 s  .  c  o  m
  2       v_dt DATE :=sysdate;
  3       v_tx VARCHAR2(2000);
  4  begin
  5       v_tx:=to_char(v_dt,'mm/dd/yyyy');
  6       DBMS_OUTPUT.put_line(v_tx);
  8       v_tx:=to_char(v_dt,'hh24:mi');
  9       DBMS_OUTPUT.put_line(v_tx);
 10       v_tx:=to_char(v_dt);
 11      DBMS_OUTPUT.put_line(v_tx);
 12  end;
 13  /

PL/SQL procedure successfully completed.

The built-in function SYSDATE returns the current date and time for the server on which the database resides.

Oracle uses its default date format from the database initialization parameter NLS_DATE_FORMAT which is 'DD-MON-RRRR'.

NLS stands for National Language Support, also known as Globalization Support.

NLS allows to you configure the database to conform to the requirements of countries that use different date and time formats.

These settings can be viewed in the NLS_SESSION_PARAMETERS and NLS_DATABASE_PARAMETERS dictionary views.

Related Topics