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



Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Greg.

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

Answered by: Tom Kyte - Last updated: November 19, 2001 - 5:40 am UTC

Category: Database - Version: 8.1.6

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: SUM is better than DISTINCT

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 we 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;


and you rated our response

  (1 rating)

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


Thanks a ton to TOM

November 19, 2001 - 5:40 am UTC

Reviewer: Sankar from India

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,