ORACLE Procedure with TIMESTAMP

Question

I try to write a procedure to know the datetime from a timezone to another timezone I can't compile because the format date is not valid and I don't know how to manage the variable. Can you give me a tip, please

    create or replace PROCEDURE DATETIME_FROM_TO
(
    VALUE$DATETIME in date, 
    VALUE$FROM in varchar2,
    VALUE$TO in varchar2,
    FLAG in number
) AS  c1 SYS_REFCURSOR;  
    begin
        VALUE_DATETIME := VALUE$DATETIME;
        VALUE_FROM := VALUE$FROM;
        VALUE_TO := VALUE$TO;
        if FLAG=12 then
            open c1 for            
            select to_char(from_tz(TIMESTAMP 'VALUE_DATETIME', 'VALUE$FROM') at time zone 'VALUE$TO','DD/MM/YYYY HH:MI PM') as localtime into c1 from dual;
        else
            open c1 for
            select to_char(from_tz(TIMESTAMP 'VALUE$DATETIME', 'VALUE$FROM') at time zone 'VALUE$TO','DD/MM/YYYY HH24:MI') as localtime into c1 from dual;
        end if;
        DBMS_SQL.RETURN_RESULT(c1);
        close c1;
    end;

thank you

_"I can't compile because the format date is not valid"_ That's not the only reason it won't compile. Your code has more errors than an early Mets gaem. What about statements like "VALUE_DATETIME := VALUE$DATETIME;" You didn't declare variable VALUE_DATETIME. Also, C1 is the name of your cursor. Don't select "into" a cursor name. In fact, for the SELECT that drives a cursor, you don't SELECT "into" anything. You deal with that when you FETCH. And I always use CURSOR FOR loops, so I never have to worry about FETCH. And you OPEN your cursor, but you never loop over it to do anything. — EdStevens, Mar 26 '21 at 19:09
In addition to my previous comment, I really don't like your naming convention of your input parms. 'VALUE$DATETIME'. While the compiler accepts the '$', I'd avoid it simply because it _looks_ like some sort of substitution variable, because that's how '$' is interpreted in other contexts. I name all of my parms 'P_something', and all of my declared variables 'v_something'. Very clear, and no 'double takes'. — EdStevens, Mar 26 '21 at 19:12

score 1 · Accepted Answer · answered Mar 26 '21 at 20:03

I'm not sure why you want to go down the hugely complicated route of using a cursor and returning a string in a result set.

Just write a simple function that takes a TIMESTAMP argument (if you pass it a DATE it will be implicitly cast to a TIMESTAMP) and if you want to format it as a string then you can call TO_CHAR on the return value as required.

CREATE FUNCTION change_timezone(
  i_datetime TIMESTAMP,
  i_from_tz  VARCHAR2,
  i_to_tz    VARCHAR2
) RETURN TIMESTAMP WITH TIME ZONE DETERMINISTIC
IS
BEGIN
  RETURN FROM_TZ( i_datetime, i_from_tz ) AT TIME ZONE i_to_tz;
END;
/

Then you can call it using:

SELECT change_timezone( DATE '1970-01-01', 'UTC', 'Europe/Berlin' )
FROM   DUAL;

Which outputs (depending on your NLS_TIMESTAMP_TZ_FORMAT setting):

| EPOCH_TIME_IN_GERMANY             |
| :-------------------------------- |
| 1970-01-01 01:00:00.000000000 CET |

and if you want it with a particular format then:

SELECT TO_CHAR(
         change_timezone( DATE '1970-01-01', 'UTC', 'Europe/Berlin' ),
         'DD/MM/YYYY HH12:MI PM'
       ) AS formatted_epoch_time_in_germany
FROM   DUAL;

Which outputs:

| FORMATTED_EPOCH_TIME_IN_GERMANY |
| :------------------------------ |
| 01/01/1970 01:00 AM             |

If you really want to incorporate the string conversion (don't, do the conversion outside the function) then you could use:

CREATE FUNCTION format_as_timezone(
  i_datetime TIMESTAMP,
  i_from_tz  VARCHAR2,
  i_to_tz    VARCHAR2,
  i_use_24hr NUMBER DEFAULT 1
) RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
  RETURN TO_CHAR(
    FROM_TZ( i_datetime, i_from_tz ) AT TIME ZONE i_to_tz,
    CASE i_use_24hr
    WHEN 1
    THEN 'DD/MM/YYYY HH24:MI'
    ELSE 'DD/MM/YYYY HH12:MI AM'
    END
  );
END;
/

db<>fiddle here

Thank you MT0, it's working fine. It helps me a lot. – Zako Mar 29 '21 at 08:42 — Zako, Mar 29 '21 at 08:42

ORACLE Procedure with TIMESTAMP

1 Answers1