Home » SQL & PL/SQL » SQL & PL/SQL » BST timezone query error
BST timezone query error [message #584533] Wed, 15 May 2013 02:23 Go to next message
HemaV
Messages: 6
Registered: July 2012
Location: Bangalore
Junior Member
Hi All,

when i give dates which falls under 31-Mar to 31-Oct my query works fine as in Query1. if i give any other dates as in Query2 its throwing me error "ORA-01857: not a valid time zone".

Can't i use the below query to find out the BST time?

Query1:
select
TO_TIMESTAMP_TZ (TO_CHAR ('03/31/2013 06:25:57')||' BST',
'mm/dd/yyyy hh24:mi:ss TZD'
) AT TIME ZONE 'Europe/London'
from dual

Query2:
select
TO_TIMESTAMP_TZ (TO_CHAR ('03/15/2013 06:25:57')||' BST',
'mm/dd/yyyy hh24:mi:ss TZD'
) AT TIME ZONE 'Europe/London'
from dual


Thanks in advance,
Re: BST timezone query error [message #584538 is a reply to message #584533] Wed, 15 May 2013 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
TO_CHAR ('03/31/2013 06:25:57')


TO_CHAR of a string is something silly, isn't it?
TO_TIMESTAMP_TZ (or the like) and formats are missing.

If your default timestamp format was correct for the string you gave it'd work:
SQL> select
  2  TO_TIMESTAMP_TZ (TO_CHAR ('03/31/2013 06:25:57')||' BST',
  3  'mm/dd/yyyy hh24:mi:ss TZD'
  4  ) AT TIME ZONE 'Europe/London'
  5  from dual 
  6  /
31/03/2013 05:25:57.000 EUROPE/LONDON

1 row selected.

SQL> select
  2  TO_TIMESTAMP_TZ (TO_CHAR ('03/15/2013 06:25:57')||' BST',
  3  'mm/dd/yyyy hh24:mi:ss TZD'
  4  ) AT TIME ZONE 'Europe/London'
  5  from dual
  6  /
15/03/2013 04:25:57.000 EUROPE/LONDON

1 row selected.

But as you can see, NEVER rely on default format.

Regards
Michel
Re: BST timezone query error [message #584542 is a reply to message #584538] Wed, 15 May 2013 04:16 Go to previous messageGo to next message
HemaV
Messages: 6
Registered: July 2012
Location: Bangalore
Junior Member
Hi,

If the value is within the date range '03/31/2013 02:00:00' and '10/27/2013 01:59:59' then below query is working and if it falls out of this range then it's not working. I'm confused why it works fine if the date falls under BST time (from Last sunday of March till last sunday of October) and not for other dates Sad...

select
TO_TIMESTAMP_TZ ('03/15/2013 06:25:57'||' BST',
'mm/dd/yyyy hh24:mi:ss TZD'
) AT TIME ZONE 'Europe/London'
from dual

NOTE: we are using Oracle 11 version.

Thanks,
Re: BST timezone query error [message #584549 is a reply to message #584542] Wed, 15 May 2013 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
It works fine for me:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> select
TO_TIMESTAMP_TZ ('01/15/2013 06:25:57'||' BST',
'mm/dd/yyyy hh24:mi:ss TZD'
) AT TIME ZONE 'Europe/London'
from dual;  2    3    4    5

TO_TIMESTAMP_TZ('01/15/201306:25:57'||'BST','MM/DD/YYYYHH24:MI:SSTZD')ATTIM
---------------------------------------------------------------------------
15-JAN-13 05.25.57.000000000 AM EUROPE/LONDON


What exact version are you using?
Re: BST timezone query error [message #584567 is a reply to message #584542] Wed, 15 May 2013 10:36 Go to previous message
Michel Cadot
Messages: 59158
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I checked in 9.2.0.8, 10.2.0.4, 11.2.0.1 (should upgrade this one), it works for all.
Use SQL*Plus and copy and paste what you do and get like cookiemonster and I did.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Wed, 15 May 2013 10:37]

Report message to a moderator

Previous Topic: Using Bind Variable
Next Topic: Funzione Sha-256
Goto Forum:
  


Current Time: Mon Sep 22 07:12:45 CDT 2014

Total time taken to generate the page: 0.25422 seconds