Home » SQL & PL/SQL » SQL & PL/SQL » Count number of "Feb 29"s (ORACLE 10.2.0)
Count number of "Feb 29"s [message #396684] Tue, 07 April 2009 11:51 Go to next message
emaildp
Messages: 2
Registered: April 2009
Location: Asker, Norway
Junior Member
Hi
Could someone help me please? Smile
I need a quick method to which takes two date values as parameters and returns the number of "Feb 29"s between them.
Basic idea is to trim all the leap years and omit the extra day on all the calculations involved in a specific transaction.
Cheers
/Yaka
Re: Count number of "Feb 29"s [message #396686 is a reply to message #396684] Tue, 07 April 2009 12:14 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@emaildp,

Please post what you have already tried.

POSSIBLE HINT:
1. FLAG the value as 1 wherever you encounter the required date, the rest as 0 like:
DATE_1    FLAG_VALUE
--------- ----------
24-FEB-08          0
25-FEB-08          0
26-FEB-08          0
27-FEB-08          0
28-FEB-08          0
29-FEB-08          1
01-MAR-08          0
02-MAR-08          0


2. SUM up the FLAG_Value from the above example and you will get the number of leap years.

Links:
Oracle Row Generator Techniques
Decode Function

Hope this helps.

Regards,
Jo
Re: Count number of "Feb 29"s [message #396809 is a reply to message #396684] Wed, 08 April 2009 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> def beg=to_date('24/02/2007','DD/MM/YYYY')
SQL> def end=to_date('02/03/2009','DD/MM/YYYY')
SQL> with 
  2    years as (
  3      select extract(year from &beg) 
  4             + case when &beg <= trunc(&beg,'year')+59 then 0 else 1 end
  5             + level - 1 year
  6      from dual
  7      connect by level <= 
  8         extract(year from &end) - extract(year from &beg) + 1
  9         - case when &end <= trunc(&end,'year')+59 then 1 else 0 end
 10         - case when &beg <= trunc(&beg,'year')+59 then 0 else 1 end
 11    )
 12  select count(*) nb_29_feb
 13  from years
 14  where ( mod(year,4) = 0 and mod(year,100) != 0 )
 15     or mod(year,400) = 0
 16  /
 NB_29_FEB
----------
         1

1 row selected.

Regards
Michel
Re: Count number of "Feb 29"s [message #396853 is a reply to message #396809] Wed, 08 April 2009 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or there's the calculating way of doing it:
with src as (select sysdate  start_date, sysdate+2500 end_date from dual)
select start_date
      ,end_date
      ,floor((extract(year from end_date) - extract(year from start_date))/4) --one_per_4_years
      + case when mod((extract(year from end_date) - extract(year from start_date)),4) > mod(extract(year from end_date),4) then 1 else 0 end --one_in_spare_years
      - case when extract(month from start_date) > 2 and mod(extract(year from start_date),4)=0 then 1 else 0 end --start_Date_after_feb_29
      - case when extract(month from end_date) <= 2  and mod(extract(year from end_date)  ,4)=0 then 1 else 0 end --end_Date_before_feb_29
      as leap_years
from src;


Michels does have the advantage of being able to deal with dates before 1900 and after 2100, but it also put an almost measurably greater load on the server
Re: Count number of "Feb 29"s [message #397196 is a reply to message #396853] Thu, 09 April 2009 06:43 Go to previous messageGo to next message
emaildp
Messages: 2
Registered: April 2009
Location: Asker, Norway
Junior Member
Thank you all.
I still wonder why Oracle doesn't provide a straight fw method to tackle leap year issues.
Cheers
/
Re: Count number of "Feb 29"s [message #397207 is a reply to message #397196] Thu, 09 April 2009 07:14 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Who says Oracle doesn't?
Oracle is very well aware of leap years, it's just that they did not provide a built-in function for every query one can come up with.
I wonder how you would do this in a system/language that provides a more straightforward way of handling leap years as you put it..
Re: Count number of "Feb 29"s [message #397877 is a reply to message #397207] Tue, 14 April 2009 06:45 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
or there is the generic way, to try the day after the 28th of feb:
WITH years AS
     (SELECT (LEVEL - 1) offs FROM DUAL CONNECT BY LEVEL <= 100)
SELECT offs, ADD_MONTHS(TO_DATE('28/02/2000', 'DD/MM/YYYY') + 1, 12 * offs) yr
  FROM years

gives
OFFS	YR

0	29.02.2000
1	28.02.2001
2	28.02.2002
3	28.02.2003
4	29.02.2004
5	28.02.2005
6	28.02.2006
7	28.02.2007
8	29.02.2008
9	28.02.2009
Re: Count number of "Feb 29"s [message #397879 is a reply to message #397877] Tue, 14 April 2009 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How does this answer the question?

"Count number of "Feb 29"s between 2 dates"

Regards
Michel
Re: Count number of "Feb 29"s [message #397892 is a reply to message #397879] Tue, 14 April 2009 07:55 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Quote:

How does this answer the question?


It gives a subquery to decide, if a year is a leap year or not. The rest is shown in the other posts, so there is room to combine and learn. You told me (right) some times:
Quote:

When responding to questions, if it is obviously a student with a homework assignment or someone else just learning (especially in the homework forum), it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

Re: Count number of "Feb 29"s [message #397900 is a reply to message #397892] Tue, 14 April 2009 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The topic is advanced, it is not the first step (started a week ago) and solutions have already been provided, so I doubt someone (including me) will have a closer look to what you posted.
And I don't see what is your point with:
Quote:
try the day after the 28th of feb:

and
Quote:
YR
29.02.2000
28.02.2001
28.02.2002
28.02.2003
29.02.2004
28.02.2005
28.02.2006
28.02.2007
29.02.2008
28.02.2009

which contains 28th feb and not the day after. Every year has a 28th Feb.
And what about years before 2000?

Regards
Michel
Re: Count number of "Feb 29"s [message #397903 is a reply to message #397900] Tue, 14 April 2009 08:16 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
may be I have a bad day or You ?!
YR
29.02.2000 ---> 29.02 !
28.02.2001 ---> no
28.02.2002 ---> no
28.02.2003 ---> no
29.02.2004 ---> 29.02 !
28.02.2005 ---> no
28.02.2006 ---> no
28.02.2007 ---> no
29.02.2008 ---> 29.02 !
28.02.2009 ---> no

It is a very simple algorithm with no need to know, what year is why a leap year (mod 4 and/or mod 1000) - let ORACLE try it.
Previous Topic: Can Foriegn Key Constarint Deferred?
Next Topic: Index Monitoring Usage
Goto Forum:
  


Current Time: Fri Feb 14 18:03:14 CST 2025