Change Date in Oracle using FIXED_DATE

In our previous project for testing purpose, we required to change Sysdate in database.
Changing date is useful for many functional testing to go back date and perform functional compliance operations.

Oracle provide FIXED_DATE, system level parameter for changing date.
Please note, these parameter is at System level, hence its impact is across session i.e. System level.

Lets suppose, fixed date is not use and we have current real-time date setup as below.
SELECT SYSDATE , CURRENT_DATE , SYSTIMESTAMP FROM DUAL;

SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ ---------------------------------------
12-DEC-2016 12-DEC-2016 12-12-16 12:58:09.295000000 PM +05:30


We will change date to backdated one, for instance 05-Jan-2015.

ALTER SYSTEM SET FIXED_DATE='05-JAN-2015';

Using FIXED_DATE parameter, we had set reset Sysdate to 05-Jan-2015.

Now let re run above query to check Sysdate, Current Date and Systimestamp information.

SYSDATE CURRENT_DATE SYSTIMESTAMP
----------- ------------ ---------------------------------------
05-JAN-2015 12-DEC-2016 12-12-16 01:02:56.743000000 PM +05:30


Only SYSDATE change using FIXED_DATE parameter, CURRENT_DATE and SYSTIMESTAMP shows AS OF Date/Time information.
It it important to understand, it will impact only sysdate at database level.

Now let reset date to a constant date with time component.

ALTER SYSTEM SET FIXED_DATE='05-JAN-2015 10:12:11';

If NLS_DATE_FORMAT is not set properly, it would give below exceptions.

SQL Error: ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02078: invalid setting for ALTER SYSTEM FIXED_DATE
02097. 00000 - "parameter cannot be modified because specified value is invalid"

Reset NLS_DATE_FORMAT and let try again.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SYSTEM SET FIXED_DATE='05-JAN-2015 10:12:11';

Now it will successful and now time component is also FIXED.
Let recheck Different date function values.

SYSDATE CURRENT_DATE SYSTIMESTAMP
---------------------- ---------------------- ---------------------------------------
05-JAN-2015 10:12:11 12-DEC-2016 13:10:00 12-12-16 01:10:00.419000000 PM +05:30

Now we have SYSDATE with Time component as FIXED, it wont change!.
Time component will be FIXED, it wont changed!

CURRENT_DATE and SYSTIMESTAMP has no impact with FIXED_DATE Parameter.

When we are done with our testing, we need to reset FIXED_DATE to get actual real time date information.
We can reset using “NONE”

ALTER SYSTEM SET FIXED_DATE=NONE;

SYSDATE CURRENT_DATE SYSTIMESTAMP
---------------------- ---------------------- ---------------------------------------
12-DEC-2016 13:13:58 12-DEC-2016 13:13:58 12-12-16 01:13:58.636000000 PM +05:30


It reset to real time date informations.