Oracle DATE Data Type

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 ranges from January 1, 4712 BCE to December 31, 9999 CE (Common Era, or ‘AD’). By default, Oracle uses CE date entries if BCE is not used explicitly.

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

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 NLS_DATE_FORMAT parameter uses the default value derived from NLS_TERRITORY.

First, show the NLS_TERRITORY parameter:

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

Output:

VALUE ---------- AMERICA Code language: SQL (Structured Query Language) (sql)

It shows America.

Second, show 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:

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

Third, retrieve the current date with the standard date format using the SYSDATE function.

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

Output:

SYSDATE --------- 10-MAY-25Code language: SQL (Structured Query Language) (sql)

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

Fourth, change the NLS_DATE_FORMAT for the current session:

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

Finally, verify the change by executing the statement that displays the current system date again:

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

You should see a date value that matches the new date format:

SYSDATE ---------- 2025-05-10Code language: SQL (Structured Query Language) (sql)

Formatting 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.

The following statement displays the current system date in a specific format, you use the TO_CHAR() function as follows:

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

The output is:

RESULT ------------- May 10, 2025Code language: SQL (Structured Query Language) (sql)

The NLS_DATE_LANGUAGE parameter controls the language that the TO_CHAR() function uses for displaying the month name.

If you want to change the current language to another, such as French, you can 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 change:

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

The following is the output:

RESULT ------------- Mai 10, 2025Code language: SQL (Structured Query Language) (sql)

The output indicates that the month name is in French.

Converting a string to a 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:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH'; SELECT TO_DATE( 'August 01, 2017', 'MONTH DD, YYYY' ) FROM dual; Code language: SQL (Structured Query Language) (sql)

The output value is:

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

Notice that we have to change the language back to English before using the TO_DATE function with English month names (August).

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.

Using the Oracle DATE data type with tables #

First, create 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)

Try it

The my_events table has two columns with the DATE data type, which are start_date and end_date.

Second, insert a new row into the my_events table:

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)

Try it

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

Third, insert another row into the my_events table. In this example, we use the TO_DATE() function to convert a string to a date before inserting:

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)

Try it

Fourth, insert one more row into my_events table:

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)

Try it

Fifth, retrieve all rows from the my_events table:

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

Output:

Sixth, format date events using the TO_CHAR() function:

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)

Try it

Output:

The following example shows how to filter events by date:

SELECT * FROM my_events WHERE start_date > DATE '2017-09-30';Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Working with both date and time #

Oracle DATE type includes both date and time. The DATE type can store the time with a precision of up to seconds. Additionally, the time does not have time zone information.

To store the date and time with the precision up to fractional seconds with time zone information, you should use TIMESTAMP WITH TIME ZONE data type.

Let’s take an example of using the DATE data type to store both date and time.

First, create a table called appointments:

CREATE TABLE appointments ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_name VARCHAR(255) NOT NULL, appointment_time DATE NOT NULL ); Code language: SQL (Structured Query Language) (sql)

Second, insert data into the appointments table:

INSERT INTO appointments (customer_name, appointment_time) VALUES ( 'Jane Doe', TO_DATE ('2025-05-10 15:30:00', 'YYYY-MM-DD HH24:MI:SS') );Code language: SQL (Structured Query Language) (sql)

In this example, we use the TO_DATE function to convert a date and time string to a DATE value before inserting into the table:

TO_DATE('2025-05-10 15:30:00', 'YYYY-MM-DD HH24:MI:SS')Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the appointments table:

SELECT customer_name, appointment_time FROM appointments;Code language: SQL (Structured Query Language) (sql)

Output:

To show both date and time, you need to change the NLS_DATE_FORMAT for the current session:

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

And retrieve data from the appointments table:

SELECT customer_name, appointment_time FROM appointments;Code language: SQL (Structured Query Language) (sql)

Output:

Summary #

Use Oracle DATE data type to handle date and time data.
Use the TO_DATE() function to convert a string to a date based on a specific format.
Use the TO_CHAR() function to format a date.

Was this tutorial helpful?