Oracle SQL: Current Date and Time

Getting the Current Date and Time

In any language, it’s important to know how to get the current date and time. How to do that is often one of the first questions to come up, especially in applications that involve dates in any way, as most applications do.

Up through Oracle8i Database, you had one choice for getting the date and time in PL/SQL: you used the SYSDATE function, and that was it. Beginning with Oracle9i Database, you have all the functions in Table at your disposal, and you need to understand how they work and what your choices are.

Table. Comparison of functions that return current date and time

Function	Time zone	Datatype returned
CURRENT_DATE	Session	DATE
CURRENT_TIMESTAMP	Session	TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP	Session	TIMESTAMP
SYSDATE	Database server	DATE
SYSTIMESTAMP	Database server	TIMESTAMP WITH TIME ZONE

The Oracle CURRENT_DATE function returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE. The format in which the date is displayed depends on NLS_DATE_FORMAT parameter. The default setting of NLS_DATE_FORMAT is DD-MON-YY. This returns a 2-digit day, a three-character month abbreviation, and a 2-digit year.

Example Usage:

CURRENT_DATE

SYSDATE

SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

To see the current system date and time with the time zone, use just the CURRENT_DATE function. 

To see the current system date and time with fractional seconds and the time zone, use the following statement: 

SELECT SYSTIMESTAMP FROM dual;

For example to see the current date in the format "Wednesday, 18th September, 2019", use the following syntax:

SELECT TO_CHAR(sysdate,'Day, ddth Month, yyyy')"Today" FROM dual;

This will return the following (assuming today is 9/18/2019):

TODAY

------------------------

Wednesday, 18th September, 2019