I needed to determine whether a year was a leap year and was able to successfully use his code snippet.

mod(:year, 4)
If the result is 0 then it's a leap year.

>
mod(:year, 4)
-- If the result is 0 then it's a leap year.
>
This is wrong!

Years divisible by 4 are leap years
BUT
Years divisible by 100 are NOT leap years
BUT
Years divisible by 400 ARE leap years

1900 was not a leap year, 2000 was a leap year

In terms of using Oracle functions to answer the question of
how many calendar days in a calendar year, this cannot be
questioned. However, this brings back memories of my prior
life of doing financial calculations. In the USA at least,
these questions are much more difficult. For example,
depending on State law, and when the payments of a load are
made, a year can turn out to have anywhere from 358 to 367 days. So, if the question is not really about leap years,
but really about days to collect interest, I urge you to
contact your compliance officer.

Hello Tom,

It is interested when I used your defined query to the
year 2000. Output always is 366 days.
Rest of the years are fine. Even year 1900 gives 365 days.
why is that?
select add_months(trunc(to_date('01-jan-2000'),'year'), 12) -
trunc(to_date('01-jan-2000'),'year') from dual;

366

Dawar:

Because 2000 was a leap year, 366 is the correct NOD...

HTH,

Mark

Hi Tom,

last_day(to_date('01-FEB-1500','dd-mon-yyyy')) returns 29-FEB-1500. Is it correct ? If not, then why it is like that?

I have tested it in 8.1.7 under HP-UX 11.0.

Thanks.

Tom

I was able to reproduce SBB's problem with 1500:

SQL> select last_day(to_date('01-FEB-1500','dd-mon-yyyy')) from dual;

LAST_DAY(
---------
29-FEB-00

This is wrong because 1500 was not a leap year - divisble by 100 but not 400.  This is also a problem with 1400, etc.  It isn't a problem with 1700, 1800 or 1900.  Nor does it apply to 2500, 2600 or 2900.

Yes, I have too much time on my hands - it's lunchtime and it's raining - but this does imply that Oracle's date generation lacks a consistent algorithm.  Another good reason for hoping that a wormhole in the spacetime continuum doesn't open up and send us back to the Middle Ages :P

Cheers, APC 

Hi,

1200 is a leap year
1300 is a leap year
1400 is a leap year
1500 is a leap year
1600 is a leap year
1700 is not a leap year
1800 is not a leap year
1900 is not a leap year
2000 is a leap year
...

Why ?

Because in Gregorian calendar xx00 is a leap year if and only if xx is divisible by 4.
But what about 1300, 1400 and 1500?
Gregorian calendar started during the night of 4 october 1582 to 15 october 1582. Before there was the Julian calendar in which all of the years divisible by 4 are leaps.

select to_char(to_date('04/10/1582','DD/MM/YYYY')+1,'DD/MM/YYYY') from dual;
TO_CHAR(TO
----------
15/10/1582

Regards

Up until 1582 any year divisibly by 4 qualified as a leap year. After this Pope Gregory XIII added that centennial years should also be divisible by 400 to qualify.

I look forward to the day when we go metric with this...

100 mins in an hour
10 hours in a day
10 days in a month
10 months in a year

Simple!

:-)
Connor

I understood that we can use Last_Day to find out leap year but my situation is i will get from and to date as input parameters eg:

15/07/2003 from date
14/07/2004 to date

So the month feb falls in 2004 so the select should fetch Last_Day of feb-2004

another example

02/02/2004 from date
01/02/2005 to date

here i need to take the later one and find out whether it falls in leap year!

How can i do this?

This site is fantastic for learning useful work-related stuff, but I never thought the day would come when I'd be reading AskTom for info about Popes and history and gregorians - and wormholes!!! Bring on Who Wants To Be a Millionaire!

<Quote>

$ cal 02 1500

</Quote>

Can we know the script cal.sql you are running ??? Its Just excellent.

I've been fascinated with the whole calendar reform issue ever since I was a kid, so thought I'd share this little historical oddity with your readers.

select * from v$version;

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

select to_date('10-4-1582','MM-DD-YYYY') from dual;

10/4/1582

select to_date('10-5-1582','MM-DD-YYYY') from dual;

10/15/1582

select to_date('10-10-1582','MM-DD-YYYY') from dual;

10/15/1582

select to_date('10-15-1582','MM-DD-YYYY') from dual

10/15/1582

select add_months(trunc(to_date('01-jan-1582'),'year'), 12) -
trunc(to_date('01-jan-1582'),'year') from dual;

355

None of the above should be surprising. When Pope Gregory XIII adopted the
Gregorian Calendar in 1582, 10 days were dropped from October. Thus, the day
after October 4 became October 15. Oracle reflects this.

So far, so good. But now, try this from a Solaris system prompt:

csh> uname -a

SunOS kaos 5.8 Generic_117000-01 sun4u sparc SUNW,Ultra-4

csh> cal 10 1582

October 1582
S M Tu W Th F S
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

Hmmmm. This looks like a normal 31-day month to me. What gives?

Well, my interest in calendars over the years prompted me to try this next:

csh> cal 09 1752

September 1752
S M Tu W Th F S
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Aha!

Back in Oracle:

select add_months(trunc(to_date('01-jan-1752'),'year'), 12) -
trunc(to_date('01-jan-1752'),'year') from dual

366

So, to Oracle, 1752 is a perfectly ordinary (leap) year.

Why the difference? Is Oracle "wrong"? Or is the problem with Solaris?

Well, neither, as it turns out. The issue is actually one of politics. When the Pope adopted the Gregorian calendar, Catholic countries tended to make the switch pretty quickly. Protestant countries, however, didn't like the Pope telling them what to do, so they balked.

The United Kingdom (along with her American colonies), finally made the switch in 1752. Thus, Solaris reflects Anglo-American history, whereas Oracle goes along with the original Gregorian calendar.

As a side note, other countries took even longer to make the switch, in particular those with an Orthodox Christian background. For instance, the Russians didn't make the switch until around 1918, which is why the anniversary of the "October Revolution" (on the old Julian calendar) currently falls in November (on the Gregorian calendar). Because the changeover was so recent, dates in Russian history frequently are referred to as "Old Style" versus "New Style".

At any rate, this disagreement as to when the Gregorian calendar actually began could conceivably lead to some confusion for anyone using Oracle to store historical dates, if they're performing date arithmetic on them.

Lest one thinks that Julian dates are the way around these
date issues (surely, there's no variance in them, right?),
yes, there's a well-known, outstanding Julian date bug w/
Oracle. (Bug #106242) See Steve Adams' write-up at:
</code> http://www.ixora.com.au/notes/julian_zero.htm <code>for the full story! ;-)

Dear Tom,

Lets assume start date is 15/07/2003 and the end is date 14/07/2004.Here first thing we have to find whether feb falls inbetween this period if yes then which year it belongs to. In this above example yes it is in 2004.So
we need to find 2004 is leap or not.
The second example the starts is 02/02/2004 and the end date is 01/03/2005.Here the start falls in feb 2004 and end date also crosses feb 2005. So we need to find both years 2004 and 2005 whether leap or not.


Thanks

ops$tkyte@ORA9IR2> variable x varchar2(20);
ops$tkyte@ORA9IR2> variable y varchar2(20)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :x := '15/07/2003';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec :y := '14/07/2004';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select last_day( add_months( trunc( to_date(:x,'dd/mm/yyyy'),'mm'), r ) )
  2    from
  3  (select rownum-1 r
  4     from all_objects
  5    where rownum <=
  6          ceil(months_between(last_day(to_date(:y,'dd/mm/yyyy')),
  7               trunc(to_date(:x,'dd/mm/yyyy'),'mm')))
  8  )
  9  where to_char( add_months( trunc( to_date(:x,'dd/mm/yyyy'),'mm'), r ), 'mm' ) = '02'
 10  /
 
LAST_DAY(
---------
29-FEB-04
 
ops$tkyte@ORA9IR2>
 

Dear Tom,

You are THE MAN. Thanks a lot.

Regards,
Ibrahim.

I was looking for the logic to create a function which will give me the result of DAYS360 function in Microsoft Excel.
Can any of the Oracle Date functions be used for this purpose?

unless perhaps you're a financial analyst....

"DAYS360 calculates the number of days between dates, based
on the 360 day year used in interest calculations."

Also, apparently, there is a "US method", and a "European
method"!

Personally, I like my years to be around 365.2425 days long.
;-)

I was trying to look up something else, which I knew I had commented on, so I searched on myself

Then I found this thread, and just had to recall what I had said.

Then I uncovered this dare, and just had to supply the function (not enough room here to comment):

create or replace function days360(
       p_start_date           date,
       p_end_date             date,
       p_rule_type            char default 'F'
       )
    RETURN number
IS
  v_mm1    pls_integer;
  v_dd1    pls_integer;
  v_yyyy1  pls_integer;
  v_mm2    pls_integer;
  v_dd2    pls_integer;
  v_yyyy2  pls_integer;
BEGIN
  v_yyyy1 := to_number(to_char(p_start_date,'yyyy'));
  v_mm1   := to_number(to_char(p_start_date,'mm'));
  v_dd1   := to_number(to_char(p_start_date,'dd'));
  v_yyyy2 := to_number(to_char(p_end_date,'yyyy'));
  v_mm2   := to_number(to_char(p_end_date,'mm'));
  v_dd2   := to_number(to_char(p_end_date,'dd'));
  IF p_rule_type = 'F' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31
          THEN IF v_dd1 < 30
                    THEN v_dd2 := 1;
                         v_mm2 := v_mm2 + 1;
                         IF v_mm2 = 13 THEN v_mm2 := 1;
                                            v_yyyy2 := v_yyyy2 +1;
                         END IF;
                    ELSE v_dd2 := 30;
               END IF;
     END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30;
               IF  (v_dd1 < 30)
                   THEN v_dd2 := 1;
                        v_mm2 := 3;
               END IF;
     END IF;
     IF v_mm2 IN (4, 6, 9, 11) AND v_dd2 = 30
          AND v_dd1 < 30
          THEN v_dd2 := 1;
               v_mm2 := v_mm2 + 1;
     END IF;
  ELSIF p_rule_type = 'T' THEN
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_dd1 = 31 THEN v_dd1 := 30; END IF;
     IF v_mm1 = 2  AND v_dd1 = to_number(to_char(last_day(p_start_date),'dd'))
          THEN v_dd1 := 30; END IF;
     IF v_dd2 = 31 THEN v_dd2 := 30; END IF;
     IF v_mm2 = 2  AND v_dd2 = to_number(to_char(last_day(p_end_date),'dd'))
          THEN v_dd2 := 30; END IF;
  ELSE RAISE_APPLICATION_ERROR('-20002','3VL Not Allowed Here');
  END IF;
  RETURN (v_yyyy2 - v_yyyy1) * 360
       + (v_mm2 - v_mm1) * 30
       + (v_dd2 - v_dd1);
END;
/

As an example, try the function with beginning and ending dates of 20060731 and 20070731. Then try these pairs:

     Start Date Pay Date
 ----------- -------------
 20060731 20060831
 20060831 20060930
 20060930 20061031
 20061031 20061130
 20061130 20061231
 20061231 20070131
 20070131 20070228
 20070228 20070331
 20070331 20070430
 20070430 20070531
 20070531 20070630
 20070630 20070731

Add the days together. Consider if you will ever trust a bank again.