遇到一个比较棘手的取间隔时间问题.要写一个存储过程,传入三个参数,开始时间和结束时间(YYYY-MM-DD格式的字符串),还有一个是选择间隔(周/月/季度/半年/年).若选择为周,那么要得到起止时间内的每周一和周日的具体日期写入一张表中.举例如下:
开始时间为2007-08-01,结束时间为2007-08-31,间隔为周.那么我要得到如下数据.
2007-08-06 2007-08-12
2007-08-13 2007-08-19
2007-08-20 2007-08-26

若选择间隔为月,那么要得到起止时间内的每月1日至每月末.
如开始时间为2007-06-12,结束时间为2007-10-20,那么得到如下数据
2007-07-01 2007-07-31
2007-08-01 2007-08-31
2007-09-01 2007-09-30

若选择间隔为季度,那么就要得到起止时间内的每季度的第一天和最后一天.
举上面的例子来说.那么要得到的就是:
2007-07-01 2007-09-30

以此类推,若间隔为半年就是取起止时间内的半年头和半年尾,若间隔为年,就是起止时间内的年头和年尾啦.[@more@]

代码:


create or replace procedure test
(

  date_beg      in varchar2,

  date_end      in varchar2,

  duration_type in varchar2
)

as

  beg_date      date;

  end_date      date;

  beg_month     date;

  end_month     date;
begin

  if to_date(date_beg,'yyyy-mm-dd')>to_date(date_end,'yyyy-mm-dd') then

     dbms_output.put_line('beginning date must be earlier than end date');

  else

     beg_date := to_date(date_beg,'yyyy-mm-dd');

     end_date := to_date(date_end,'yyyy-mm-dd');

     if duration_type='week' then

        while beg_date+6<=end_date loop

           if to_char(beg_date,'D')='2' then

              while beg_date+6<=end_date loop

                 insert into test2 values(to_char(beg_date,'yyyy-mm-dd'),to_char(beg_date+6,'yyyy-mm-dd'));

                 beg_date := beg_date+7;

              end loop;

           end if;

           beg_date := beg_date+1;

        end loop;

     else

        if to_char(beg_date,'dd')='01' then

           beg_month := beg_date;

        else

           beg_month := to_date(to_char(add_months(beg_date,1),'yyyy-mm')||'-01','yyyy-mm-dd');

        end if;

        if to_char(end_date,'yyyy-mm-dd')=to_char(last_day(end_date),'yyyy-mm-dd') then

           end_month := end_date;

        else

           end_month := to_date(to_char(last_day(add_months(end_date,-1)),'yyyy-mm-dd'),'yyyy-mm-dd');

        end if;

        if duration_type='month' then

           while to_char(beg_month,'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

              insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(beg_month),'yyyy-mm-dd'));

              beg_month := add_months(beg_month,1);

           end loop;

        elsif duration_type='quarter' then

           while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

              if to_char(beg_month,'mmdd') in ('0101','0401','0701','1001') then

                 while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

                    insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,2)),'yyyy-mm-dd'));

                    beg_month := add_months(beg_month,3);

                 end loop;

              end if;

              beg_month := add_months(beg_month,1);

           end loop;

        elsif duration_type='semiyear' then

           while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

              if to_char(beg_month,'mmdd') in ('0101','0701') then

                 while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

                    insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,5)),'yyyy-mm-dd'));

                    beg_month := add_months(beg_month,6);

                 end loop;

              end if;

              beg_month := add_months(beg_month,1);

           end loop;

        end if;

     end if;

  end if;

  commit;

  

  exception

  when others then

       dbms_output.put_line(sqlerrm);
end;

/


SQL> exec test('2007-06-01','2009-11-30','semiyear');


PL/SQL procedure successfully completed.


SQL> SELECT * FROM TEST2;


BEG_DATE   END_DATE
---------- ----------
2007-07-01 2007-12-31

2008-01-01 2008-06-30

2008-07-01 2008-12-31

2009-01-01 2009-06-30




.

__________________
http://atgc.itpub.net

ITPub博客

取间隔时间

最新文章