How to change Oracle sysdate?

Introduction

In this article, we will show a method to change sysdate on the Oracle database. Although sysdate holds the current date from the operating system on which the database has been installed, there is a way to change it on the database level by setting special FIXED_DATE parameter. That feature should be extremely useful for testing purposes.

FIXED_DATE parameter

FIXED_DATE enables you to set a constant date that will be returned by SYSDATE command instead of the current system date.

Property	Description
Parameter type	String
Syntax	`FIXED_DATE = YYYY-MM-DD-HH24:MI:SS (or the default Oracle date format)
Default value	There is no default value.
Modifiable	ALTER SYSTEM
Basic	No

Altering FIXED_DATE queries used the following syntax:

ALTER SYSTEM SET FIXED_DATE = [YYYY-MM-DD-HH24:MI:SS (or the default Oracle date format) | NONE]

To reset fixed date setting use FIXED_DATE=NONE like in the following SQL:

ALTER SYSTEM SET FIXED_DATE=NONE

Example

In the following example we will set a constant Oracle SYSDATE with 2019-06-06-12:00:00 value:

CopyALTER SYSTEM SET FIXED_DATE=2019-06-06-12:00:00';

From now on every query which use SYSDATE will return provided date:

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

Result:

TO_CHAR(SYSDATE,'DD-MM-YYYY')

06-06-2019

Conclusion

The FIXED_DATE parameter is useful mainly for testing. We can use the default Oracle date format to set a constant date that will be returned in every SQL query that uses SYSDATE command. Just keep in mind that FIXED_DATE will be fixed forever until we undo that setting using FIXED_DATE=NONE command.