1 PACKAGE BODY MTH_TZ_UTIL AS
2 /*$Header: mthtzutb.pls 120.0.12010000.4 2008/09/09 00:51:40 tkan noship $*/
3
4 /* *****************************************************************************
5 * Function : CONVERT_TZ *
6 * Description : This function is used to return the Date by passed *
7 * date, source and target time zone *
8 * File Name : MTHTZUTS.PLS *
9 * Visibility : Public *
10 * Parameters : date, from_tz, to_tz *
11 * Return Value : date *
12 * Modification log : *
13 * Author Date Change *
14 * Tawen Kan 01-Mar-2007 Initial Creation *
15 ***************************************************************************** */
16
17 Function convert_tz (date_time date
18 ,from_tz varchar2
19 ,to_tz varchar2) RETURN DATE IS return_date date;
20 t_date date;
21 from_tz_name varchar2(64);
22 to_tz_name varchar2(64);
23 BEGIN
24 t_date := date_time;
25 SELECT tzname INTO from_tz_name FROM V$TIMEZONE_NAMES WHERE tzabbrev = from_tz AND ROWNUM = 1;
26 SELECT tzname INTO to_tz_name FROM V$TIMEZONE_NAMES WHERE tzabbrev = to_tz AND ROWNUM = 1;
27
28 IF date_time IS NOT null AND from_tz IS NOT null AND to_tz IS not null
29 AND from_tz_name IS NOT NULL AND to_tz_name IS NOT NULL THEN
30 BEGIN
31 return_date := to_timestamp_tz(to_char(t_date,'YYYY-MM-DD HH24:MI:SS') || ' ' || from_tz_name, 'YYYY-MM-DD HH24:MI:SS TZR') at time zone to_tz_name;
32 EXCEPTION
33 WHEN others THEN
34 RAISE_APPLICATION_ERROR (-20001, 'Exception has occured');
35 END;
36 ELSE
37 return_date := date_time;
38 END IF ;
39 RETURN return_date;
40 END convert_tz;
41
42 /* *****************************************************************************
43 * Function : FROM_TZ *
44 * Description : This function is used to return the Date by passed *
45 * date, source time zone *
46 * File Name : MTHTZUTS.PLS *
47 * Visibility : Public *
48 * Parameters : date, from_tz *
49 * Return Value : date *
50 * Modification log : *
51 * Author Date Change *
52 * Tawen Kan 01-Mar-2007 Initial Creation *
53 ***************************************************************************** */
54
55 Function from_tz (date_time date
56 ,from_tz varchar2) RETURN DATE IS
57 return_date date;
58 t_date date;
59 from_tz_name varchar2(64);
60 BEGIN
61 t_date := date_time;
62 SELECT tzname INTO from_tz_name FROM V$TIMEZONE_NAMES WHERE tzabbrev = from_tz AND ROWNUM = 1;
63
64 IF date_time IS NOT NULL AND from_tz IS NOT NULL AND from_tz_name IS NOT NULL THEN
65 BEGIN
66 return_date := to_timestamp_tz(to_char(t_date,'YYYY-MM-DD HH24:MI:SS') || ' ' || from_tz_name, 'YYYY-MM-DD HH24:MI:SS TZR') at time zone 'GMT';
67 EXCEPTION
68 WHEN OTHERS THEN
69 RAISE_APPLICATION_ERROR (-20001, 'Exception has occured');
70 END;
71 ELSE
72 return_date := date_time;
73 END IF;
74 RETURN return_date;
75 END FROM_TZ;
76
77 /* *****************************************************************************
78 * Function : TO_TZ *
79 * Description : This function is used to return the Date by passed *
80 * date, target time zone *
81 * File Name : MTHTZUTS.PLS *
82 * Visibility : Public *
83 * Parameters : date, to_tz *
84 * Return Value : date *
85 * Modification log : *
86 * Author Date Change *
87 * Tawen Kan 01-Mar-2007 Initial Creation *
88 ***************************************************************************** */
89
90 FUNCTION to_tz(date_time date
91 ,to_tz varchar2) RETURN DATE IS
92 return_date date;
93 t_date date;
94 to_tz_name varchar2(64);
95 BEGIN
96 t_date := date_time;
97 SELECT tzname INTO to_tz_name FROM V$TIMEZONE_NAMES WHERE tzabbrev = to_tz AND ROWNUM = 1;
98
99 IF date_time IS NOT NULL AND to_tz IS NOT NULL THEN
100 BEGIN
101 return_date := to_timestamp_tz(to_char(t_date,'YYYY-MM-DD HH24:MI:SS') || ' ' || 'GMT', 'YYYY-MM-DD HH24:MI:SS TZR') at time zone to_tz_name;
102 EXCEPTION
103 WHEN OTHERS THEN
104 RAISE_APPLICATION_ERROR (-20001, 'Exception has occured');
105 END ;
106 ELSE
107 return_date := date_time;
108 END IF;
109 RETURN return_date;
110 END to_tz;
111
112 END MTH_TZ_UTIL;
[Home] [Help]