Connor McDonald on SQL and the Oracle Database

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

```
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

%d bloggers like this:

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

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

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