How to change Oracle sysdate?

September 28, 2019 No comments Oracle sysdate ALTER System date

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. 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) | NONE
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

3. Example

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:

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

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.

{{ message }}

{{ 'Comments are closed.' | trans }}