Learning is not a spectator sport

Is a year a leap year ?

1–2 minutes

This post seems timely given that yesterday was Feb 29.

In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic.

This is perhaps one of the very very few exceptions Smile


SQL> set timing off
SQL> create or replace
  2  function is_leap_year1(y number) return boolean is
  3    x date;
  4  begin
  5    x := to_date('2902'||y,'ddmmyyyy');
  6    return true;
  7  exception
  8    when others then return false;
  9  end;
 10  /

Function created.

SQL>
SQL> create or replace
  2  function is_leap_year2(y number) return boolean is
  3  begin
  4    return mod(y,4)=0 and ( mod(y,100) != 0 or mod(y,400) = 0 );
  5  end;
  6  /

Function created.

SQL>
SQL> set timing on
SQL> declare
  2    l boolean;
  3  begin
  4   for i in 1 .. 1000000 loop
  5     l := is_leap_year1(1234);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.88
SQL>
SQL> declare
  2    l boolean;
  3  begin
  4   for i in 1 .. 1000000 loop
  5     l := is_leap_year2(1234);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.53

Although I must admit, I’m struggling to think of a use case where you would need to check a year for being a leap year hundreds of thousands of times Smile

3 responses to “Is a year a leap year ?”

  1. Jeffrey Kemp Avatar
    Jeffrey Kemp

    For no good reason at all, here’s another method which seems to be faster than is_leap_year1 (but still slower than your is_leap_year2), perhaps because it doesn’t rely on an exception to detect a non-leap year:

    create or replace
    function is_leap_year3(y number) return boolean is
    begin
    return to_char(last_day(to_date(’02’||y,’mmyyyy’)),’DD’) = ’29’;
    end;

    On my system, I get the following results:

    is_leap_year1: 9.869
    is_leap_year2: 0.024
    is_leap_year3: 4.676

    Reply
  2. Jeffrey Kemp Avatar
    Jeffrey Kemp

    And another, taken from https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:13012348049:

    create or replace
    function is_leap_year4(y number) return boolean is
    d date := trunc(to_date(y,’yyyy’),’year’);
    begin
    return add_months(d, 12) – d = 366;
    end;

    is_leap_year4: 2.739

    Reply
  3. Jeffrey Kemp Avatar
    Jeffrey Kemp

    What if the parameter to the function is already a date?

    create or replace
    function is_leap_year2d(y date) return boolean is
    yy number := to_number(to_char(y,’yyyy’));
    begin
    return mod(yy,4)=0 and ( mod(yy,100) != 0 or mod(yy,400) = 0 );
    end;

    set timing on
    declare
    l boolean;
    begin
    for i in 1 .. 1000000 loop
    l := is_leap_year2d(sysdate);
    end loop;
    end;

    Elapsed: 00:00:02.932

    create or replace
    function is_leap_year3d(y date) return boolean is
    begin
    return to_char(add_months(trunc(y,’year’),2)-1,’DD’) = ’29’;
    end;

    set timing on
    declare
    l boolean;
    begin
    for i in 1 .. 1000000 loop
    l := is_leap_year3d(sysdate);
    end loop;
    end;

    Elapsed: 00:00:02.665

    create or replace
    function is_leap_year4d(y date) return boolean is
    d date := trunc(y,’year’);
    begin
    return add_months(d, 12) – d = 366;
    end;

    set timing on
    declare
    l boolean;
    begin
    for i in 1 .. 1000000 loop
    l := is_leap_year4d(sysdate);
    end loop;
    end;

    Elapsed: 00:00:01.987

    Reply

Got some thoughts? Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trending