Count number of "Feb 29"s [message #396684] |
Tue, 07 April 2009 11:51  |
emaildp
Messages: 2 Registered: April 2009 Location: Asker, Norway
|
Junior Member |
|
|
Hi
Could someone help me please?
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   |
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   |
 |
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   |
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 #397207 is a reply to message #397196] |
Thu, 09 April 2009 07:14   |
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   |
_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 #397892 is a reply to message #397879] |
Tue, 14 April 2009 07:55   |
_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 #397903 is a reply to message #397900] |
Tue, 14 April 2009 08:16  |
_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.
|
|
|