Time zones and TO_TIMESTAMP

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Fri, 15 Feb 2002 06:33:50 -0800
Message-ID: <F001.00410E6B.20020215061821@fatcity.com>

I have a question for anyone who might have ventured to try out Oracle9i's new datetime features. I'm experimenting with TO_TIMESTAMP_TZ, and I find that it doesn't recognize time zone abbreviations as [I think] it should. For example:

SQL> select TO_TIMESTAMP_TZ ('Abril 12 1991, 15:00 CET', 2 'Month DD YYYY, hh24:mi TZD',
3 'NLS_DATE_LANGUAGE=Spanish')
4 from dual;

TO_TIMESTAMP_TZ('ABRIL121991,15:00CET','MONTHDDYYYY,HH24:M

12-APR-91 03.00.00.000000000 PM -05:00 Note that I specified CET (Central European Standard Time) in my input string, and that I placed TZD correctly in my format string, but that the results came back in Eastern Standard Time, which happens to be my session time zone.
-5:00 is EST, not CET.

Further reading of the manuals suggests that I cannot specify just a time zone abbreviation when using TO_TIMESTAMP_TZ, but that I must specify at least a time zone region, and then additionally (and optionally) an abbreviation. But the docs are unclear on this. To further confuse matters, I *can* use a time zone abbreviation in a timestamp literal. So what gives?

Best regards,

Jonathan Gennick
mailto:jonathan_at_gennick.com * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jonathan Gennick
INET: jonathan_at_gennick.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Feb 15 2002 - 08:33:50 CST

Time zones and TO_TIMESTAMP_TZ