How to pass string variable inside to_timestamp() in oracle

Question

I have a query like this:

select from_tz(to_timestamp(v_time,'YY-MM-DD HH24:MI:SS'), 'UTC') 
      at time zone 'America/New_York' from dual;

If I use a string Date instead of v_time then it works fine. But I need to pass a variable v_time inside to_timestamp, how can I do that?

Thanks in advance.

The full code is here:

DECLARE
     v_test varchar2(200);
     v_cur_time varchar2(200);
     v_local_strem_time varchar2(200);
     v_time varchar2(200);
     v_time_diff NUMBER(20);
      v_temp varchar2(200);
     BEGIN

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') into v_cur_time
    FROM DUAL;

    select substr(((select TO_CHAR(date_time,'YY-MM-DD HH24:MI:SS') from observation_measurement where observation_measurement_id=5777992)), 1,17)
    into v_time  from dual;

    v_temp := v_time;
    select from_tz(to_timestamp(v_temp,'YY-MM-DD HH24:MI:SS'), 'UTC') 
    at time zone 'America/New_York' into v_local_strem_time from dual;

      select 24 * (to_date(v_cur_time, 'YY-MM-DD HH24:MI:SS') 
             - to_date(v_local_strem_time, 'YY-MM-DD HH24:MI:SS')) into v_time_diff 
       from dual;

       DBMS_OUTPUT.PUT_LINE(v_time_diff); 
    END;

What is the datatype of `v_time` , `VARCHAR` or `DATE` ? – Maheswaran Ravisankar Feb 20 '15 at 04:00 — Maheswaran Ravisankar, Feb 20 '15 at 04:00

void · Answer 1 · 2015-02-25T14:02:44.387

the variable must be declared(in a code block or in a procedure or in a function or etc)

declare 
v_time varchar(20);
outval timestamp;
begin
v_time := '15-02-20 07:13:10';
select from_tz(to_timestamp(v_time,'YY-MM-DD HH24:MI:SS'), 'UTC') 
      at time zone 'America/New_York' into outval from dual;
dbms_output.put_line(outval);
end;

OUTPUT: 20-FEB-15 02.13.10.000000 AM

EDIT AFTER EDDITING QUESTION: the problem is with v_local_strem_time which contains AMERICA/NEW_YORK string , one way is use the SUBSTR function to remove the unnecessary string. let me to test it:

Create table observation_measurement(
observation_measurement_id number,
date_time date
)

insert into observation_measurement
values (5777992,to_date('214/10/09', 'YY-MM-DD HH24:MI:SS'));

DECLARE
     v_test varchar2(200);
     v_cur_time varchar2(200);
     v_local_strem_time varchar2(200);
     v_time varchar2(200);
     v_time_diff NUMBER(20);
     v_temp varchar2(200);
     BEGIN

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') into v_cur_time
    FROM DUAL;

    select substr(((select TO_CHAR(date_time,'YY-MM-DD HH24:MI:SS') from observation_measurement where observation_measurement_id=5777992)), 1,17)
    into v_time  from dual;

    v_temp := v_time;
    select from_tz(to_timestamp(v_temp,'YY-MM-DD HH24:MI:SS'), 'UTC') 
    at time zone 'America/New_York' into v_local_strem_time from dual;

      select 24 * (to_date(v_cur_time, 'YY-MM-DD HH24:MI:SS') 
             - to_date(substr(v_local_strem_time,0,18), 'YY-MM-DD HH24:MI:SS')) into v_time_diff 
       from dual;

       DBMS_OUTPUT.PUT_LINE(v_time_diff); 
    END;

OUTPUT: 143241

Also maybe the datatype of observation_measurement.date_time of your structure is not date, at that time you may need to do appropriate converts.

@Ferheg: I edit the question with full code. Although I declared the variable in the code block still it is showing error like: "date format picture ends before converting entire input string". I got the v_time variable from another query. Please see the edited question. — Novis, Feb 20 '15 at 13:21
@Novis answer edited, you can check it, sorry for the delay I was busy. — void, Feb 25 '15 at 14:03

How to pass string variable inside to_timestamp() in oracle

1 Answers1