Controlling SYSDATE

For certain testing scenarios, I need to have SYSDATE return a certain date. This can be done with Oracle’s FIXED_DATE parameter.

For example, if you want SYSDATE to return ‘05-OCT-2011 15:30:00’ do the following as sys:

[sourcecode language=”sql”]alter system set fixed_date = ‘2011-10-05 15:30:00’;[/sourcecode]

If you want SYSDATE back to normal and return the correct date do:

[sourcecode language=”sql”]alter system set fixed_date = none;[/sourcecode]

Note that this does not influence the behavior of SYSTIMESTAMP.

See also: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams092.htm#CHDFHBHI

Thanks to Carsten Czarski for the tip.