Solved

how does PST/PDT and TZR/TZD works.

Posted on 2014-02-12
6
1,251 Views
Last Modified: 2014-03-04
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
0
Comment
Question by:ajaybelde
  • 4
  • 2
6 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39854743
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
 

Author Comment

by:ajaybelde
ID: 39856576
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39856672
exec TZ_TST ;


you don't have a parameter
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ajaybelde
ID: 39856704
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39903739
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39903755
PROCEDURE TZ_TST
(out_put OUT SYS_REFCURSOR)


tz_tst requires a parameter
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Concat multi row values of a field in oracle 6 64
Oracle DATE Column Space 11 79
PL/SQL Display based on value 4 27
Shredding xml into an oracle 11g Database 2 44
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question