sysdate is not timezone aware, sensitive, nothing.
It is the date returned to us by the system - by the operating system.
that answer is in fact "good enough", your opinion is just that really. Mine is too. They are our opinions.
this is the way sysdate has worked since before I was using Oracle. To change the way something that is used by *every* Oracle program ever written would be a disaster.
that is my opinion.
You say "changing my applications to timezone is not possible". Think about what would happen to the entire world if we changed the default behaviour of something so heavily used?????? That, that would be a recoding effort that makes your REQUIREMENT look trivial.
Look, you have a brand new requirement (must be new, cannot be old - else you would have done something about it years ago). New requirements sometimes require......... changes to code.
... Our application contains >900 date fields and changing them to
timezone types is not possible.
......
that statement is entirely and utterly false on the fact of it. Entirely false.
Howerver:
... Our application contains >900 date fields and changing them to meet our requirements is a decision we have made....
is true, that statement is entirely true.
you used a function that is not timezone aware. It has never been time zone aware, it never will be, it would be a disaster if it was made to be (for everyone).
You need not change your 900 columns by the way, you need only to use a function that returns a timezone sensitive date for you - localtimestamp for example (return date in timezone of client session - that way you could actually consolidate your databases since the really big mistake you made here is having more than one database!)
ops$tkyte%ORA10GR2> alter session set time_zone='-04:00';
Session altered.
ops$tkyte%ORA10GR2> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
-04:00
ops$tkyte%ORA10GR2> declare
2 l_dt date := sysdate;
3 l_tz date := localtimestamp;
4 begin
5 dbms_output.put_line( 'sysdate = ' || l_dt );
6 dbms_output.put_line( 'localtimestamp = ' || l_tz );
7 end;
8 /
sysdate = 07-may-2008 01:28:33
localtimestamp = 07-may-2008 01:28:33
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> alter session set time_zone='+02:00';
Session altered.
ops$tkyte%ORA10GR2> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+02:00
ops$tkyte%ORA10GR2> declare
2 l_dt date := sysdate;
3 l_tz date := localtimestamp;
4 begin
5 dbms_output.put_line( 'sysdate = ' || l_dt );
6 dbms_output.put_line( 'localtimestamp = ' || l_tz );
7 end;
8 /
sysdate = 07-may-2008 01:28:33
localtimestamp = 07-may-2008 07:28:33
PL/SQL procedure successfully completed.