Is a year a leap year ?

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 thoughts on “Is a year a leap year ?

  1. 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

  2. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s