Oracle CURRENT_DATE

Summary: in this tutorial, you’ll learn how to use the Oracle CURRENT_DATE function to return the current date in the session time zone.

Introduction to the Oracle CURRENT_DATE function #

The CURRENT_DATE function returns the current date and time in the session time zone. The CURRENT_DATE function requires no argument:

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

The CURRENT_DATE function returns a DATE value in the Gregorian calendar. Note that the DATE includes both date and time.

Getting the current date and time example #

First, change the default date format to a new one that includes the time value:

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

Seccond, retrieve the current SESSIONTIMEZONE function:

SELECT
  SESSIONTIMEZONE
FROM
  DUAL;Code language: SQL (Structured Query Language) (sql)

Currently, the session time zone is set to -07:00.

Third, get the current date and time in the session time zone using the CURRENT_DATE function:

SELECT
  CURRENT_DATE
FROM
  DUAL;Code language: SQL (Structured Query Language) (sql)

Output:

06-AUG-2017 19:43:44Code language: SQL (Structured Query Language) (sql)

Changing session time zone #

If you change the session time zone, the value of the current date is adjusted accordingly as shown in the following example:

First, set the session time zone to -09:00:

ALTER SESSION SET TIME_ZONE = '-09:00';Code language: SQL (Structured Query Language) (sql)

Second, get the current date in the session time zone:

06-AUG-2017 17:45:33Code language: SQL (Structured Query Language) (sql)

The new current date was adjusted to about -2 hours as expected.

Using CURRENT_DATE as default value #

First, create a new table with the event_date column whose values default to the current date and time of the session time zone:

CREATE TABLE events(
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_name VARCHAR2(255) NOT NULL,
    event_date DATE DEFAULT CURRENT_DATE
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into to the events table:

INSERT INTO events(event_name)
VALUES('Oraccle CloudWorld');Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the events table:

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

You’ll see the time at the moment you execute the query in the event_date column.

Summary #

• Use the Oracle  CURRENT_DATE function to get the current date and time in the session time zone.