1 PACKAGE BODY fnd_timezones_pvt as
2 /* $Header: AFTZPVTB.pls 120.1 2005/07/02 04:20:13 appldev ship $ */
3
4 -- this package body should only be installed in a 9i db. It will not compile
5 -- or run in any db prior to 9i.
6 function adjust_datetime(date_time date
7 ,from_tz varchar2
8 ,to_tz varchar2) return date is
9 return_date date;
10 add_hour number := 0;
11 t_date date;
12 begin
13 t_date := date_time;
14 if date_time is not null
15 and from_tz is not null
16 and to_tz is not null then
17 <<try_again>>
18 begin
19 return_date := to_timestamp_tz(to_char(t_date,'YYYY-MM-DD HH24:MI:SS') || ' ' || from_tz,
20 'YYYY-MM-DD HH24:MI:SS TZR') at time zone to_tz;
21 -- exceptions handler to handle case where 'from' timezone datetime
22 -- does not exist. This will try to add one hour to the from time
23 -- until 3 hours are reached or it no longer raises the error.
24 -- also catches bug 2276107
25 exception when others then
26 if sqlcode in (-1878,-1891) then
27 add_hour := add_hour +1;
28 if add_hour > 3 then
29 raise;
30 else
31 t_date := t_date + add_hour/24;
32 goto try_again;
33 end if;
34 else
35 raise;
36 end if;
37 end;
38 else
39 return_date := date_time;
40 end if;
41 return return_date;
42 end adjust_datetime;
43
44 end fnd_timezones_pvt;
[Home] [Help]