Помогите с запросом. Количество-во 29 февраля между 2 датами

SQL> with dates
  2  as
  3  (select to_date('03-04-2003', 'DD-MM-YYYY') start_date,
  4          to_date('03-04-2023', 'DD-MM-YYYY') end_date  from dual)
  5  select count(*)
  6    from (select start_date + level - 1 dates
  7            from dates
  8          connect by start_date + level <= end_date)
  9   where to_char(dates, 'DDMM') = '2902'
 10  /

  COUNT(*)
----------
         5

SQL> 

select sum(decode(to_char(to_date('28.02.'||(to_char(sysdate,'YYYY')+D.N),
                                  'DD.MM.YYYY')+1,'DD.MM'),
                  '29.02',1,
                  0))
  from (select rownum N from dual
        connect by level < months_between(sysdate + 5000, sysdate)/12+1) D;

with t as (select sysdate d1, sysdate + 10000 d2 from dual)
,t2 as (select t.*,trunc(d1,'y') y1,trunc(d2,'y') y2 from t)
,t3 as (select d1,d2,y1,y2,last_day(add_months(y1,(level-1)*12+1)) dd  from t2
connect by level*12 <= months_between(y2,y1)+12)
select count(*) from t3 where d1 <= dd and dd <= d2 and extract(day from dd)=29

with tab as (select to_date('29-03-2000', 'DD-MM-YYYY') dt1, to_date('29-03-8000', 'DD-MM-YYYY') dt2 from dual)
select Sm
     + case
         when mod(Extract(year from Dt1), 4) = 0 and
              (mod(Extract(year from Dt1), 25) != 0 or
               mod(Extract(year from Dt1), 16) = 0) and
              (Extract(month from Dt1) <= 2) then
          1 else 0 end 
      + case
         when mod(Extract(year from Dt2), 4) = 0 and
              (mod(Extract(year from Dt2), 25) != 0 or
               mod(Extract(year from Dt2), 16) = 0) and
              (Extract(month from Dt2) > 2 or
               Extract(month from Dt2) = 2 and Extract(day from Dt2) = 1) then
          1 else 0 end
  from tab
       ,(select sum(case when mod(Yr, 4) = 0 and
                          (mod(Yr, 25) != 0 or mod(Yr, 16) = 0) then
                      1  else 0 end) Sm
          from (select Extract(year from Add_Months(Dt1, 12 * level)) Yr
                  from Tab
                connect by Trunc(Add_Months(Dt1, 12 * (level - 1)), 'y') < Trunc(Dt2, 'y')))

with tab as (select to_date('29-03-2000', 'DD-MM-YYYY') dt1, to_date('29-03-8000', 'DD-MM-YYYY') dt2 from dual)
select Sm
     + case
...

select sum(decode(to_char(to_date('28.02.'||(to_char(to_date('28-02-2000', 'DD-MM-YYYY'),'YYYY')+D.N),
                                  'DD.MM.YYYY')+1,'DD.MM'),
                  '29.02',1,
                  0))
  from (select rownum N from dual
        connect by level < months_between(to_date('28-02-2000', 'DD-MM-YYYY'), to_date('28-02-2001', 'DD-MM-YYYY'))/12+1) D;

Присоединен к:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> set timing on
SQL> with t as (select to_date('01.01.0001','dd.mm.yyyy') d1,
  2                    to_date('01.03.9999','dd.mm.yyyy') d2 from dual)
  3  select sum(case to_char(d1+level,'mmdd') when '0229' then 1 end) c
  4  from t connect by level < d2-d1;

         C
----------
      2436

Затрач.время: 00:00:27.10
SQL> ;
  1  with t as (select to_date('01.01.0001','dd.mm.yyyy') d1,
  2                    to_date('01.03.9999','dd.mm.yyyy') d2 from dual)
  3  select sum(case to_char(d1+level,'mmdd') when '0229' then 1 end) c
  4* from t connect by level < d2-d1
SQL> with t as (select to_date('01.01.0001','dd.mm.yyyy') d1,
  2                    to_date('01.03.9999','dd.mm.yyyy') d2 from dual)
  3  ,t2 as (select t.*,trunc(d1,'y') y1,trunc(d2,'y') y2 from t)
  4  ,t3 as (select d1,d2,y1,y2,last_day(add_months(y1,(level-1)*12+1)) dd  from t2
  5  connect by level*12 <= months_between(y2,y1)+12)
  6  select count(*) from t3 where d1 <= dd and dd <= d2 and extract(day from dd)=29;

  COUNT(*)
----------
      2436

Затрач.время: 00:00:00.14