Setting SYSDATE in Oracle for Testing

A while ago two very smart guys (Cristian Ruepprich and Carsten Czarski) had a conversation on Twitter about how to modify the value of SYSDATE in Oracle for testing purposes. The ability to modify the value of SYSDATE can be very valuable if you have to do time-sensitive testing.

Thankfully they did have a solution, by setting the FIXED_DATE system parameter. Here's an example on how to use it:

DECLARE
SQL> SELECT SYSDATE FROM dual;

SYSDATE
22-AUG-2012 10:20:19
SQL> -- Do this now as priviledged user (ex: SYSTEM)
SQL> -- Note: this affects the entire database (not just your session)
SQL> ALTER SYSTEM SET fixed_date='2010-01-01-14:10:00';
System altered.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
01-JAN-2010 14:10:00
SQL> -- Reset SYSDATE back to "Current Time"
SQL> ALTER SYSTEM SET fixed_date=NONE;
System altered.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
22-AUG-2012 10:21:29

They're a few things to know about FIXED_DATE before using it:

• Setting it requires access to ALTER SYSTEM. This can be mitigated by creating a procedure to handle this as a privledged user (see Tom Kyte's suggestion here).

• It affects the entire system, not just your session. If you have multiple users testing on a system then you may not be able to use it. Hopefully in the future we'll be able to modify it at a session level.

• It only affects SYSDATE and not date/time functions such as systimestamp (see Sean's comment below

The documentation for FIXED_DATE can be found here.