SYSDATE Function in Oracle

by

In Oracle Database, the SYSDATE function returns the current date and time set for the operating system on which the database server resides.

The returned value is of type DATE. 

Syntax

The syntax goes like this: 

SYSDATE

So, no arguments are required (or accepted), and there are no parentheses.

Example

Here’s an example:

SELECT SYSDATE
FROM DUAL;

Result:

06/AUG/21

This example displays the date based on the value of my system’s NLS_DATE_FORMAT parameter (which is currently DD/MON/RR). We can change this parameter, or use a function like TO_CHAR() to return the result in a different format.

Example:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;

Result:

2021-08-06

Calling SYSDATE with Parentheses

As mentioned, the SYSDATE function is called without parentheses.

Here’s what happens when we call it with parentheses:

SELECT SYSDATE()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SYSDATE()
FROM DUAL
Error at Command Line : 1 Column : 15
Error report -
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:

Fixing the SYSDATE Return Value

The FIXED_DATE initialization parameter enables you to set a constant date and time that SYSDATE will always return instead of the current date and time.

This can assist in testing situations when you need the same input data to produce the same result consistently.

See Oracle’s documentation for FIXED_DATE and also initialization parameters in general for more information.