Time Converter Script for Oracle APEX

 

declare
v_valueGMT1 number;
v_valueGMT2 number;
v_valueGMT3 varchar2(20);

v_valueIST1 number;
v_valueIST2 number;
v_valueIST3 varchar2(20);
v_sysdate date;

begin
select sysdate into v_sysdate from dual;

if :P2_SELECT_ZONE = ‘IST’ then
v_valueGMT1:=to_number(substr(:P2_TIME,0,2));
v_valueGMT2:=to_number(substr(:P2_TIME,4,5));
if v_valueGMT1 > 5 then
v_valueGMT1:=v_valueGMT1-5;
elsif v_valueGMT1=5 then
v_valueGMT1:=’00’;
else
v_valueGMT1:=24-(5-v_valueGMT1);
v_sysdate:=v_sysdate-1;
end if;
if v_valueGMT2 > 30 then
v_valueGMT2:=v_valueGMT2-30;
elsif v_valueGMT2 = 30 then
v_valueGMT2:=00;
else
v_valueGMT2:=60-(30-v_valueGMT2);
end if;
if v_valueGMT1 < 10 and v_valueGMT2 <10 then
v_valueGMT3:=’GMT’||’ 0’||v_valueGMT1||’:0’||v_valueGMT2||’ ‘||v_sysdate;
elsif v_valueGMT1 < 10 and v_valueGMT2 > 10 then
v_valueGMT3:=’GMT’||’ 0’||v_valueGMT1||’:’||v_valueGMT2||’ ‘||v_sysdate;
elsif v_valueGMT1 > 10 and v_valueGMT2 < 10 then
v_valueGMT3:=’GMT ‘||v_valueGMT1||’:0’||v_valueGMT2||’ ‘||v_sysdate;
else
v_valueGMT3:=’GMT ‘||v_valueGMT1||’:’||v_valueGMT2||’ ‘||v_sysdate;
end if;

return v_valueGMT3;

elsif :P2_SELECT_ZONE = ‘GMT’ then
v_valueIST1:=to_number(substr(:P2_TIME,0,2));
v_valueIST2:=to_number(substr(:P2_TIME,4,5));

if v_valueIST1 >= 1 and v_valueIST1 <=18 then
v_valueIST1:=v_valueIST1+5;
elsif v_valueIST1=0 then
v_valueIST1:=’00’;
elsif v_valueIST1 >=19 then
v_valueIST1:=(v_valueIST1+5)-24;
v_sysdate:=v_sysdate+1;
end if;

if v_valueIST2 > 30 then
v_valueIST2:=v_valueIST2+30;
elsif v_valueIST2 = 30 then
v_valueIST2:=00;
else
v_valueIST2:=60-(30-v_valueIST2);
end if;
if v_valueIST1 < 10 and v_valueIST2 <10 then
v_valueIST3:=’IST’||’ 0’||v_valueIST1||’:0’||v_valueIST2||’ ‘||v_sysdate;
elsif v_valueIST1 < 10 and v_valueIST2 > 10 then
v_valueIST3:=’IST’||’ 0’||v_valueIST1||’:’||v_valueIST2||’ ‘||v_sysdate;
elsif v_valueIST1 > 10 and v_valueIST2 < 10 then
v_valueIST3:=’IST ‘||v_valueIST1||’:0’||v_valueIST2||’ ‘||v_sysdate;
else
v_valueIST3:=’IST ‘||v_valueIST1||’:’||v_valueIST2||’ ‘||v_sysdate;
end if;
return v_valueIST3;

elsif :P2_SELECT_ZONE = ‘EST’ then
v_valueIST1:=to_number(substr(:P2_TIME,0,2));
v_valueIST2:=to_number(substr(:P2_TIME,4,5));

if v_valueIST1 >= 1 and v_valueIST1 <=14 then
v_valueIST1:=v_valueIST1+9;
elsif v_valueIST1=0 then
v_valueIST1:=’00’;
elsif v_valueIST1 >=15 then
v_valueIST1:=(v_valueIST1+9)-24;
v_sysdate:=v_sysdate+1;
end if;

if v_valueIST2 > 30 then
v_valueIST2:=v_valueIST2+30;
elsif v_valueIST2 = 30 then
v_valueIST2:=00;
else
v_valueIST2:=60-(30-v_valueIST2);
end if;
if v_valueIST1 < 10 and v_valueIST2 <10 then
v_valueIST3:=’IST’||’ 0’||v_valueIST1||’:0’||v_valueIST2||’ ‘||v_sysdate;
elsif v_valueIST1 < 10 and v_valueIST2 > 10 then
v_valueIST3:=’IST’||’ 0’||v_valueIST1||’:’||v_valueIST2||’ ‘||v_sysdate;
elsif v_valueIST1 > 10 and v_valueIST2 < 10 then
v_valueIST3:=’IST ‘||v_valueIST1||’:0’||v_valueIST2||’ ‘||v_sysdate;
else
v_valueIST3:=’IST ‘||v_valueIST1||’:’||v_valueIST2||’ ‘||v_sysdate;
end if;
return v_valueIST3;

elsif :P2_SELECT_ZONE = ‘CST’ then

return ‘CST’;
end if;
end;

Leave a Reply

avatar
  Subscribe  
Notify of