Search

Top 60 Oracle Blogs

Recent comments

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