How to insert and retrieve dates in Oracle ?

Problem:

You want to insert dates into a table and retrieve them.

Solution

Oracle, by default the uses the format DD-MON-YYYY to represent a date, where

1. DD represents a two-digit day.
2. MON represents first three letters of the month, e.g., FEB.
3. YYYY is a four-digit year, e.g., 1968

Let us take a look at an example of adding a row to the customers table, which contains a DATE column named dob.

The following INSERT adds a row to the customers table, setting the dob column to 05-FEB-1968:

Example

INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES (7, 'James', 'Bond', '31-DEC-2000', '007-007-0007');

You can also use the DATE keyword to supply a date literal to the database. The date must use the date format YYYY-MM-DD, where

1. YYYY is a four-digit year.
2. MM is a two-digit month from 1 to 12.
3. DD is a two-digit day.

Example

INSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES (11, 'Roger', 'Federer', DATE '1983-10-01', '001-001-0001');

By default, the database returns dates in the format DD-MON-YY, where YY are the last two digits of the year.

CONVERTING DATETIMES USING TO_CHAR() AND TO_DATE()

The Oracle database has functions that enable you to convert a value in one data type to another.We will use the TO_CHAR() and TO_DATE() functions to convert a datetime to a string and vice versa.

TO_CHAR() to Convert a Datetime to a String

TO_CHAR(x [, format]) to convert the datetime x to a string. We can also provide an optional format for x. An example format is MONTH DD, YYYY, where

1. MONTH is the full name of the month in uppercase.
2. DD is the two-digit day.
3. YYYY is the four-digit year.

The following query uses TO_CHAR() to convert the dob column from the customers table to a string with the format MONTH DD, YYYY

Example

SELECT customer_id, TO_CHAR(dob, 'MONTH DD, YYYY')
FROM customers;

The next query gets the current date and time from the database using the SYSDATE function, then converts the date and time to a string using TO_CHAR() with the format MONTH DD, YYYY, HH24:MI:SS. The time portion of this format indicates that the hours are in 24-hour format and that the minutes and seconds are also to be included in the string.

Example

SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY, HH24:MI:SS')
FROM dual;
NOVEMBER  13, 2020, 10:04:11

TO_DATE() to Convert a String to a Datetime

We can use TO_DATE(x [, format]) to convert the x string to a datetime. We can provide an optional format string to indicate the format of x. If we omit format, the date must be in the default database format (usually DD-MON-YYYY or DD-MON-YY).

The following query uses TO_DATE() to convert the strings “31-DEC-2020” and “31-DEC-20” to the date December 31, 2020. However, the final date is displayed in the default format of DD-MON-YY.

Example

SELECT TO_DATE('31-DEC-2020'), TO_DATE('31-DEC-20')
FROM dual;

Finally, a DBA can help you set the default date format in Oracle.

A DBA can change the setting of NLS_DATE_FORMAT by setting this parameter’s value in the database’s init.ora or spfile.ora file, both of which are read when the database is started. A DBA can also set NLS_DATE_FORMAT using the ALTER SYSTEM command. You can also set the NLS_DATE_FORMAT parameter for your own session using SQL*Plus, which you do by using the ALTER SESSION command

Example

ALTER SESSION SET NLS_DATE_FORMAT = 'MONTH-DD-YYYY';