Skip to Main Content
  • Questions
  • Date Manipulation When Testing Home Grown Applications


Question and Answer

Tom Kyte

Thanks for the question, Greg.

Asked: January 25, 2001 - 1:08 pm UTC

Last updated: November 19, 2001 - 5:40 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Assuming my applications are getting their 'current date' from sysdate, then doing internal processing, I have a testing question. I work for a financial institution that requires us to test ahead a quarter or a year, several times a year (similar to Y2K testing). I am looking for an easy way to change the date for my applications without affecting other applications that are residing on the same app. server. I amy also have several instances of Oracle, and do not want to change the dates of those instances. Setting the date at the server is out of the question, bucause of too many other dependencies.

We have thought about using an external date driver to read into the application, but that means more overhead. With the mainframe testing, we use a tool by Viasoft that overrides the 'current_date' for each CICS region or at the batch job level. We are looking for such a solution at the client/server level. Any suggestions?

and Tom said...

Short of using a function

create or replace function my_sysdate return date
return sysdate;

instead of SYSDATE, I do not see any alternative other then at the instance level (eg: you can set a fixed_date init.ora parameter/alter system for the INSTANCE so that sysdate will be fixed at some point in time for that instance) or at the OS level (which would affect all instances on that machine).

So, if you have your own INSTANCE to test in, using FIXED_DATE might be a viable alternative.

sys@TKYTE816> select sysdate from dual;


sys@TKYTE816> alter system set fixed_date = '26-jan-02';

System altered.

sys@TKYTE816> select sysdate from dual;



  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment


Thanks a ton to TOM

Sankar, November 19, 2001 - 5:40 am UTC

Now a days, instead of searching the ORACLE docs, first i am looking at this site for re-solution of any issues/clarifications.

In worst case, i am getting the solution for any problem in not more than 5 minutes

Kudos to Tom,

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library