times_pack.sql
Download Script
create or replace package times as
function prev_quarter_start(date_in IN DATE) return date;
function prev_quarter_end(date_in IN DATE) return date;
function prev_month_start(date_in DATE) return date;
function prev_month_end(date_in DATE) return date;
function prev_week_start(date_in DATE) return date;
function prev_week_end(date_in DATE) return date;
function prev_day_start(date_in DATE) return date;
function prev_day_end(date_in DATE) return date;
function gmt_date(date_in DATE default sysdate) return date;
PRAGMA RESTRICT_REFERENCES (prev_quarter_end, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_quarter_start, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_month_start, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_month_end, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_week_start, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_week_end, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_day_start, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (prev_day_end, WNDS, WNPS);
PRAGMA RESTRICT_REFERENCES (gmt_date, WNDS, WNPS);
end times;
/
create or replace package body times as
function prev_quarter_start(date_in IN DATE) return date is
v_prev_quarter_start date;
begin
v_prev_quarter_start := trunc(last_day(add_months(date_in,-4))) + 1;
return (v_prev_quarter_start);
end prev_quarter_start;
--
function prev_quarter_end(date_in IN DATE) return date is
v_prev_quarter_end date;
begin
v_prev_quarter_end := trunc(last_day(add_months(date_in,-1))) + 1;
return (v_prev_quarter_end);
end prev_quarter_end;
--
function prev_month_start(date_in IN DATE) return date is
v_prev_month_start date;
begin
v_prev_month_start := trunc(last_day(add_months(date_in,-2))) + 1;
return (v_prev_month_start);
end prev_month_start;
--
function prev_month_end(date_in IN DATE) return date is
v_prev_month_end date;
begin
v_prev_month_end := trunc(last_day(add_months(date_in,-1))+1);
return (v_prev_month_end);
end prev_month_end;
--
function prev_week_start(date_in IN DATE) return date is
v_prev_week_start date;
begin
v_prev_week_start := trunc(date_in) - 7;
return (v_prev_week_start);
end prev_week_start;
--
function prev_week_end(date_in IN DATE) return date is
v_prev_week_end date;
begin
v_prev_week_end := trunc(date_in);
return (v_prev_week_end);
end prev_week_end;
--
function prev_day_start(date_in IN DATE) return date is
v_prev_day_start date;
begin
v_prev_day_start := trunc(date_in) - 1;
return (v_prev_day_start);
end prev_day_start;
--
function prev_day_end(date_in IN DATE) return date is
v_prev_day_end date;
begin
v_prev_day_end := trunc(date_in);
return (v_prev_day_end);
end prev_day_end;
--
function gmt_date(date_in in DATE default sysdate) return date is
v_gmt_time date;
v_gmt_delta number;
v_daylight number;
begin
select cur_value into v_gmt_delta from instance_preferences
where pref_code = 'DELGMT';
select to_number(cur_value) into v_daylight from instance_preferences
where pref_code = 'DAYLON';
v_gmt_time := date_in - ((v_gmt_delta + v_daylight) / 24);
return (v_gmt_time);
end gmt_date;
--
end times;
/
drop public synonym times;
create public synonym times for ops$oracle.times;
grant execute on times to public;