The Essential Guide to Oracle DATE Data Type By Examples

Summary: in this tutorial, you will learn about Oracle DATE data type and how to handle date and time values effectively.

Introduction to Oracle DATE data type

The DATE data type allows you to store point-in-time values that include both date and time with a precision of one second.

The DATE data type stores the year (which includes the century), the month, the day, the hours, the minutes, and the seconds. It has a range from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). By default, Oracle uses CE date entries if BCE is not used explicitly.

Oracle Database has its own propriety format for storing date data. It uses fixed-length fields of 7 bytes, each corresponding to century, year, month, day, hour, minute, and second to store date data.

Oracle date format

The standard date format for input and output is DD-MON-YY e.g., 01-JAN-17 which is controlled by the value of the NLS_DATE_FORMAT parameter.

The following statement shows the current value of the NLS_DATE_FORMAT parameter:

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_FORMAT';
Code language: SQL (Structured Query Language) (sql)

In our Oracle Database system, the value of NLS_DATE_FORMAT is:

DD-MON-RRCode language: SQL (Structured Query Language) (sql)

The following statement returns the current date with the standard date format by using the SYSDATE function.

SELECT
  sysdate
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

The result is:

01-AUG-17
Code language: SQL (Structured Query Language) (sql)

Suppose, you want to change the standard date format to YYY-MM-DD, you use the ALTER SESSION statement to change the value of the NLS_DATE_FORMAT parameter as follows:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';Code language: SQL (Structured Query Language) (sql)

To verify the change, you can execute the statement that displays the current system date again:

SELECT
  sysdate
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

You should see the following value that matches with the date format to which you have changed.

2017-08-01
Code language: SQL (Structured Query Language) (sql)

Format date using TO_CHAR() function

The TO_CHAR() function takes a DATE value, formats it based on a specified format, and returns a date string.

For example, to display the current system date in a specific format, you use the TO_CHAR() function as follows:

SELECT
  TO_CHAR( SYSDATE, 'FMMonth DD, YYYY' )
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

The output is:

August 1, 2017
Code language: SQL (Structured Query Language) (sql)

The language that the TO_CHAR() function used for displaying the month name is controlled by the NLS_DATE_LANGUAGE parameter:

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_LANGUAGE';Code language: SQL (Structured Query Language) (sql)

The output in our database system is:

AMERICANCode language: SQL (Structured Query Language) (sql)

If you want to change the current language to another e.g., FRENCH, you use the ALTER SESSION statement:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';Code language: SQL (Structured Query Language) (sql)

Now, you can execute the TO_CHAR() function again to see the effect:

SELECT
  TO_CHAR( SYSDATE, 'FMMonth DD, YYYY' )
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

The following is the output:

Août 1, 2017Code language: SQL (Structured Query Language) (sql)

As you can see, the name of the month has been changed from English to French.

Convert string to date

Because Oracle uses an internal format for storing the DATE data, you often have to convert a string to a date value before storing it in the date column.

To convert date values that are not in the standard format, you use the TO_DATE() function with a format string.

The following example converts the string 'August 01, 2017' to its corresponding date using the TO_DATE() function:

SELECT
  TO_DATE( 'August 01, 2017', 'MONTH DD, YYYY' )
FROM
  dual;   Code language: SQL (Structured Query Language) (sql)

The output value is:

01-AUG-17Code language: SQL (Structured Query Language) (sql)

which is the standard date format.

Date literals

Besides using the TO_DATE() function, you can specify a DATE value as a string literal using the following syntax:

DATE 'YYYY-MM-DD'Code language: SQL (Structured Query Language) (sql)

Notice that to use a DATE as a literal, you must use the Gregorian calendar. The following example shows the ANSI date literal of August 1^st, 2017:

DATE '2017-08-01'
Code language: SQL (Structured Query Language) (sql)

The ANSI date literal does not have a time part and must be in exact format (‘YYYY-MM-DD‘). If you want to include time data in a date value, you must use the TO_DATE() function as introduced above.

Oracle DATE data type example

The following statement creates a table named my_events:

CREATE TABLE my_events (
    event_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    event_name VARCHAR2 ( 255 ) NOT NULL,
    location VARCHAR2 ( 255 ) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY ( event_id ) 
);Code language: SQL (Structured Query Language) (sql)

In this table, we have two columns with the DATE data type, which are start_date and end_date.

To insert a new row into the my_events table, you use the following statement:

INSERT INTO my_events
            (event_name,
             location,
             start_date,
             end_date)
VALUES     ( 'TechEd Europe',
        'Barcelona, Spain',
            DATE '2017-11-14',
            DATE '2017-11-16' ); Code language: SQL (Structured Query Language) (sql)

In this example, we used two date literals in the INSERT statement.

You can use the TO_DATE() function to convert a string to a date before inserting as shown in the following example:

INSERT INTO my_events
            (event_name,
             location,
             start_date,
             end_date)
VALUES     ( 'Oracle OpenWorld',
        'San Francisco, CA, USA',
            TO_DATE( 'October 01, 2017', 'MONTH DD, YYYY' ),
            TO_DATE( 'October 05, 2017', 'MONTH DD, YYYY')); Code language: SQL (Structured Query Language) (sql)

Let’s insert one more row for testing.

INSERT INTO my_events
                (event_name,
                 location,
                 start_date,
                 end_date)
    VALUES     ( 'TechEd US',
            'Las Vegas, NV, USA'
                DATE '2017-09-25',
                DATE '2017-09-29' );Code language: SQL (Structured Query Language) (sql)

The following query returns all rows from the my_events table:

SELECT
  *
FROM
  my_events;  
Code language: SQL (Structured Query Language) (sql)

You can, of course, use the TO_CHAR() function to format the dates of events:

SELECT
  event_name, 
  location,
  TO_CHAR( start_date, 'FMMonth DD, YYYY' )  start_date, 
  TO_CHAR( end_date, 'FMMonth DD, YYYY' ) end_date 
from
  my_events;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the Oracle Date data type and how to handle Date data effectively.

Was this tutorial helpful?