Vladimir M. Zakharychev wrote:
> uday wrote:
>> I have to convert given time from "EST" to any of the following
>> timezones
>> Poland
>> Etc/GMT-3
>> ACT
>> Etc/GMT-2
>> Etc/GMT-10
>> GMT
>> Etc/GMT-9
>> Etc/GMT-1
>> CET
>> CST
>> Etc/GMT+6
>> US/Pacific
>> EST
>> PST
>> AGT
>> Hongkong
>>
>> I cannot use java stored-procedure {the JAVA support is not installed
>> at the production server}
>>
>> I have to use it from within oracle tried using new_time but
>> not-supporting all above timezone
>>
>>
>> some-body please help
>
> SELECT * FROM V$TIMEZONE_NAMES will show you all time zone names Oracle
> supports. All time zone names you listed are present there (at least in
> 10.1.0.5.) To convert a time stamp to another time zone you can use
> this syntax:
>
> SELECT :timestamp_var AT TIME ZONE 'timezone-name' FROM DUAL;
>
> For example,
>
> SQL> ALTER SESSION SET TIME_ZONE='EST';
>
> Session altered.
>
> SQL> SELECT current_timestamp AT TIME ZONE 'Poland' FROM DUAL;
>
> CURRENT_TIMESTAMPATTIMEZONE'POLAND'
> ---------------------------------------------------------------------------
> 18-SEP-06 10.24.48.577559 PM POLAND
>
> SQL> select current_timestamp from dual;
>
> CURRENT_TIMESTAMP
> ---------------------------------------------------------------------------
> 18-SEP-06 04.24.49.489085 PM EST
>
> Note that the returned value will be TIMESTAMP WITH TIME ZONE, but you
> can convert it to a DATE easily.
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
My recommendation will likely be to use the FROM_TZ function. But I am
still not convinced that I understand the OP's intent.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Mon Sep 18 2006 - 18:29:25 CDT
