Hi,
I have declared expiration start date and end date variables. The expiration Start Date always start from 1st of the Month or 15th of the Month. Right now I am incrementing the expiration start date by one Month :
for example if my exp start date : 2010-01-01
and exp end date : 2010-04-01
I dissect the dates from start date :
2010-01-01
2010-02-01
2010-03-01
2010-04-01
Instead of incrementing the start date by 1 month as am currently doing I would want to increment by 1st and 15th of every month :
2010-01-01
2010-01-15
2010-02-01
2010-02-15
2010-03-01
2010-03-15
2010-04-01
I tired directly to add days but that does not work. Please suggest is there a way to increment my exp_start_date variable so that it increments by exaclty 1st or 15th of the month.
Below is the code :
declare
exp_start_date varchar2(15):='2010-01-01';
exp_end_date varchar2(15) :='2010-04-01';
v_exp_start_dt date := to_date(exp_start_date,'yyyy/mm/dd');
v_exp_end_dt date := to_date(exp_end_date,'yyyy/mm/dd');
v_tmp integer;
begin
while v_exp_start_dt <= v_exp_end_dt loop
select nvl(sum(dc.daily_count),0) into v_tmp
from daily_counts_summary dc
where
and dc.expiration_dt = v_exp_start_dt
and v_exp_start_dt between v_exp_start_dt and v_exp_end_dt
v_exp_start_dt := add_months(v_exp_start_dt,+1) ;
end loop;
end;
Thanks,
eva