Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: timezone variable in PL/SQL

    I am trying to write some pl/sql and pass in a timezone variable to convert timestamps, etc.

    It seems like this is not possible?

    If anyone has any suggestions on how to pass the "US/Eastern" into test #2 I would greatly appreciate it.

    here is my simple test case:
    Code:
    SQL> declare
      2  v_timestamp timestamp;
      3
      4  begin
      5
      6  select
      7  from_tz(to_timestamp('03052008 12:00', 'mmddyyyy hh24:mi'), 'UTC') at time zone 'US/Eastern'
      8  into v_timestamp
      9  from dual;
     10
     11  dbms_output.put_line( 'BEGIN test 1 '||to_char(v_timestamp, 'mmddyyyy HH24:MI'));
     12
     13  end;
     14  /
    BEGIN test 1 03052008 07:00
    
    PL/SQL procedure successfully completed.
    
    SQL> declare
      2  v_tz varchar2(10) := 'US/Eastern';
      3  v_timestamp timestamp;
      4
      5  begin
      6
      7  select
      8  from_tz(to_timestamp('03052008 12:00', 'mmddyyyy hh24:mi'), 'UTC') at time zone v_tz
      9  into v_timestamp
     10  from dual;
     11
     12  dbms_output.put_line( 'BEGIN test 2 '||to_char(v_timestamp, 'mmddyyyy HH24:MI'));
     13
     14  end;
     15  /
    declare
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected
    ORA-06512: at line 7
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    only solution thus far was to create a function so I can pass the parameter into it (function from Natalka Roshak).
    Code:
    create or replace
    function convert_time ( datetime in timestamp, tz1 in varchar2, tz2 in varchar2 )
    return timestamp with time zone
    as
    	retval timestamp with time zone;
    begin	
    
    	retval := from_tz(datetime, tz1) at time zone tz2;
    	return retval;
    end;
    /
    
    SQL> declare
      2  v_tz varchar2(10) := 'US/Eastern';
      3  v_timestamp timestamp;
      4
      5  begin
      6
      7  select
      8  convert_time(to_timestamp('05052008 12:00', 'mmddyyyy hh24:mi'), 'UTC',v_tz)
      9  into v_timestamp
     10  from dual;
     11
     12  dbms_output.put_line( 'BEGIN test 2 '||to_char(v_timestamp, 'mmddyyyy HH24:MI'));
     13
     14  end;
     15  /
    BEGIN test 2 05052008 08:00
    
    PL/SQL procedure successfully completed.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •