Database Testing - FIXED_DATE

Years ago I learned this trick from this AskTom post. I was doing a lot of database unit testing with SQLUnit and I had the need to set the clock to a certain date. Of course I could change the system clock, but I found this more useful, especially on my own personal sandbox.

If you do this on a shared database, it will affect everyone, so be careful.

CJUSTICE@TESTING>SELECT TO_CHAR( SYSDATE, 'MM/DD/YYYY HH24:MI:SS' ) d 
FROM DUAL;

D
-------------------
04/02/2010 15:50:35

1 row selected.

CJUSTICE@TESTING>ALTER SYSTEM SET FIXED_DATE = '02-MAR-10';

System altered.

CJUSTICE@TESTING>SELECT TO_CHAR( SYSDATE, 'MM/DD/YYYY HH24:MI:SS' ) d 
FROM DUAL;

D
-------------------
03/02/2010 00:00:00

1 row selected.

CJUSTICE@TESTING>ALTER SYSTEM SET FIXED_DATE = 'NONE';

System altered.

Elapsed: 00:00:00.09
CJUSTICE@TESTING>SELECT TO_CHAR( SYSDATE, 'MM/DD/YYYY HH24:MI:SS' ) d 
FROM DUAL;

D
-------------------
04/02/2010 16:03:01

1 row selected.

Happy testing.