How to change Oracle sysdate?

1. 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.

2. Change Oracle sysdate using 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.

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

3. Example of changing sysdate in Oracle

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

ALTER 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:

4. 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.