Wednesday, November 24, 2010

Warning about whitespace and to_timestamp_tz

Quick post prompted by a stackoverflow question.


select to_timestamp_tz('11-OCT-10 04.59.59.999999999 AM -05:00',
                        'DD-MON-RR HH.MI.SSXFF AM TZR') ws,
       to_timestamp_tz('11-OCT-10 04.59.59.999999999 AM  -05:00',
                        'DD-MON-RR HH.MI.SSXFF AM TZR') no_ws
from dual

Do you notice the extra space in the second column, preceding the -05:00 timezone ?
Oracle notices it too, but isn't very intelligent in accommodating it.

WS
-------------------------------------------
NO_WS
-------------------------------------------
11-OCT-10 04.59.59.999999999 AM -05:00
11-OCT-10 04.59.59.999999999 AM +05:00


That is the result on XE and on the 11.2.0.2 edition on apex.oracle.com

No comments: