Hi,

Is it possible to get like this:

One server will have many instances which will point to different database.

Ex: India, Singapore and Australia.

When I connect to Singapore database and give select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual, it should give me India time and if I do the same thing from Singapore instance then, it should give me Singapore time.

Is this possible.

According to Prakashs43,
I did the following:

1. SQL> select dbtimezone from dual ;

DBTIME
------
+05:30

The database was created without specifying the TIME_ZONE clause.

2. SQL> select sessiontimezone from dual ;

SESSIONTIMEZONE
--------------------------------------------
+05:30

The client session was also started from India.

3. SQL> select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual;

NEW_TIME(
---------

This statement produces a NULL.

4. SQL> alter session set time_zone='Asia/Calcutta' ;

Session altered.

SQL> select sessiontimezone from dual ;

SESSIONTIMEZONE
---------------------------------------------------------------------------
Asia/Calcutta

5. SQL> select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual;
select new_time(sysdate,to_char(current_timestamp,'TZD'),'GMT') from dual
*
ERROR at line 1:
ORA-01857: not a valid time zone

How can I overcome this error message.

Thanks.