How to Perform a Date Test Case with Old or Upcoming Sysdate in Oracle? - FIXED_DATE Parameter

advertisements

_____________________________________________________________________________________________________________________

Oracle provides a parameter called FIXED_DATE to set the oracle sysdate to a fixed date which can be a prior or upcoming date. By default the fixed_date parameter value would be null or NONE. You can set the fixed_date parameter value using alter system command. This parameter mainly used for testing purpose by setting the sysdate to an old or upcoming date.

Initially the parameter value would be null and it will show the current system date.

SQL >show parameter FIXED_DATE

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

fixed_date                           string

SQL >select to_char(sysdate,'dd-mm-yyyy hh:mi') from dual;

TO_CHAR(SYSDATE,

----------------

20-09-2013 10:28

SQL >!date

Fri Sep 20 10:29:02 CEST 2013

Setting the parameter to an old date

SQL >ALTER SYSTEM SET FIXED_DATE='2012-09-20-00:00:00';

System altered.

SQL >show parameter FIXED_DATE

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

fixed_date                           string      20-SEP-12

SQL >select to_char(sysdate,'dd-mm-yyyy hh:mi') from dual;

TO_CHAR(SYSDATE,

----------------

20-09-2012 12:00

But the systimestamp will show the actual system date.

SQL >select systimestamp from dual;

SYSTIMESTAMP

-------------------------------------------

20-SEP-13 10.38.34.508112 AM +02:00

Resetting the value to default one

SQL >ALTER SYSTEM SET FIXED_DATE=NONE;

System altered.

SQL > show parameter FIXED_DATE

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

fixed_date                           string      NONE

SQL >select to_char(sysdate,'dd-mm-yyyy hh:mi') from dual;

TO_CHAR(SYSDATE,

----------------

20-09-2013 10:39

SQL > ALTER SYSTEM SET FIXED_DATE='2014-09-20-00:00:00';

System altered.

SQL >select to_char(sysdate,'dd-mm-yyyy hh:mi') from dual;

TO_CHAR(SYSDATE,

----------------

20-09-2014 12:00

_____________________________________________________________________________________________________________________

Website Stats