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

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

Introduction

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: 

declare
       variable1_dt DATE;
  ...

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

Demo

SQL>
SQL> declare--   w  ww.j  ava  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);
  7
  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  /
04/21/2018
09:49
21-APR-18

PL/SQL procedure successfully completed.
SQL>

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