Help with DATE conversion based on sessiontimezone, dbtimezone

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marty - 28 Apr 2006 19:18 GMT

I would like to convert a date value in a select statement to the
session timezone. I know I can use NEW_TIME with the timezone
abbreviations, but dbtimezone and sessiontimezone returns offsets
(-05:00 for CST etc). not timezone abbreviations. Since I want to
adjust the date by the interval
between dbtimezone and sessiontimezone, Iwas thinking something like:

select new_time(old_date,
decode(dbtimezone,'-04:00','EST','-05:00','CST','-06:00','MST','-07:00','PST','-08:00','YST'),
decode(sessiontimezone,'-04:00','EST','-05:00','CST','-06:00','MST','-07:00','PST','-08:00','YST'))

but this doesn't seem very elegant, and I don't think very efficient
for a large number of records. Any suggestions?

Reply to this Message

Brian Peasland - 28 Apr 2006 19:53 GMT

Have you looked at the TO_CHAR function and the datetime format masks to
do your conversion?

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements
004.htm#i34510

HTH,
Brian

Signature

===================================================================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Reply to this Message

Vladimir M. Zakharychev - 29 Apr 2006 13:10 GMT

> I would like to convert a date value in a select statement to the
> session timezone. I know I can use NEW_TIME with the timezone
[quoted text clipped - 9 lines]
> but this doesn't seem very elegant, and I don't think very efficient
> for a large number of records. Any suggestions?

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> select from_tz(cast(sysdate as timestamp),dbtimezone) from dual;

FROM_TZ(CAST(SYSDATEASTIMESTAMP),DBTIMEZONE)
---------------------------------------------------------------------------
29.04.06 16:05:22,000000 EUROPE/MOSCOW

SQL> alter session set time_zone='America/New_York';

Session altered.

SQL> select from_tz(cast(sysdate as timestamp),dbtimezone) at local
from dual;

FROM_TZ(CAST(SYSDATEASTIMESTAMP),DBTIMEZONE)ATLOCAL
---------------------------------------------------------------------------
29.04.06 08:06:06,000000 AMERICA/NEW_YORK

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Reply to this Message