how does PST/PDT and TZR/TZD works.

select to_timestamp_tz ('Mon feb11 12:00:56 PDT 2013', 'Dy Mon DD HH24:MI:SS TZR YYYY') from dual
                        *
ERROR at line 1:
ORA-01882: timezone region not found


but this works.

select to_timestamp_tz ('Mon feb11 12:00:56 PST 2013', 'Dy Mon DD HH24:MI:SS TZR YYYY') from dual;

TO_TIMESTAMP_TZ('MONFEB1112:00:56PST2013','DYMONDDHH24:MI:SSTZRYYYY')
---------------------------------------------------------------------------
11-FEB-13 12.00.56.000000000 PM PST

select to_timestamp_tz ('Mon feb11 12:00:56 PST 2013', 'Dy Mon DD HH24:MI:SS TZD  YYYY') from dual;

TO_TIMESTAMP_TZ('MONFEB1112:00:56PST2013','DYMONDDHH24:MI:SSTZDYYYY')
---------------------------------------------------------------------------
11-FEB-13 12.00.56.000000000 PM -05:00

can you share me some good info on works PST/PDT or CST/CDT and TZR/TZD
ajaybeldeAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
From the Oracle SQL Reference


TZD
Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.



TZR
Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.



http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51079


In otherwords....

TZR is the "standardized" name for the zone.
TZD represents both the location and the daylight saving status  (PST vs PDT)
0
 
ajaybeldeAuthor Commented:
create or replace
PROCEDURE TZ_TST
(out_put OUT SYS_REFCURSOR)
AS
BEGIN
  open out_put for
  select to_timestamp_tz ('Mon feb 15 00:00:56 PST 2013', 'Dy Mon DD HH24:MI:SS TZD YYYY') from dual;

  NULL;
END TZ_TST;


when iam calling this got an error.

exec TZ_TST ;
BEGIN TZ_TST ; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TZ_TST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

how to call the proc
0
 
sdstuberCommented:
exec TZ_TST ;


you don't have a parameter
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ajaybeldeAuthor Commented:
SQL> exec TZ_TST ;
BEGIN TZ_TST ; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TZ_TST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

is not working anything wrong in procedure
0
 
sdstuberCommented:
why the penalty?

your last comment was simply a repeat of your previous comment and I had already answered it.

exec TZ_TST ;

is incorrect syntax.  
you don't have a parameter
0
 
sdstuberCommented:
PROCEDURE TZ_TST
(out_put OUT SYS_REFCURSOR)


tz_tst requires a parameter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.