Friday, April 2, 2010

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.

5 comments:

oraclenude said...

So cool!

oraclenerd said...

I've found that not that many people know about it, so it seemed appropriate to share. I've had arguments with DBAs about it who swore up and down that it wasn't possible.

Marco Gralike said...

Its a very very very old parameter, that goes back at least until Oracle 7.0...

oraclenerd said...

Way before my time Marco...way before. I think I started with 8i back in 2002. I came late to the game. :)

Joel Garry said...

I totally missed this since forever, thanks.