FIXED_DATE

There are so many little known parameters in oracle that sometimes you are taken by surprise. Today I found out about FIXED_DATE parameter and I did not know about it till now. Did you? May be...

This parameter is used to set a constant date for SYSDATE pseudo column. Once set, SYSDATE would always return the constant date. However, the problem is that it's not modifiable at session level which means it may not be really useful.

How to set FIXED_DATE :-

ALTER SYSTEM SET FIXED_DATE='2008-02-02-00:00:00';

SQL> select sysdate from dual;

SYSDATE
---------
02-FEB-08

Note: The format can be as shown above or the oracle default date format.

How to unset :-

ALTER SYSTEM SET FIXED_DATE=NONE;

SQL> select sysdate from dual;

SYSDATE
---------
27-FEB-08

BUT, setting this parameter does not effect SYSTIMESTAMP. It continues to show current timestamp.

SQL> ALTER SYSTEM SET FIXED_DATE='2008-02-02-00:00:00';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
02-FEB-08

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-FEB-08 09.01.07.669041 AM +05:30

Since this is a very old parameter (I think it was introduced in Oracle 8), my guess is SYSTIMESTAMP has been left out as its relatively new.