SQL & PL/SQL

Knowledge Sharing:

--The Leap Year Rule: Years evenly divisible by 4 are leap years, except for centurial years that are not evenly divisible by 400.
--E.g., the years 1700, 1800, 1900 and 2100 are not leap years, but 1600, 2000, and 2400 are leap years.

--accounts for leap day births for code executions on last day of feb. in non-leap year (months_between does not).
floor( (to_number(to_char(sysdate,'yyyymmdd')) - to_number(to_char(person.dob,'yyyymmdd'))) / 10000 ) age
---------------------
--find birthdays today with leap babies celebrating 1st march
select *
from person
where 1=1
and to_char ( sysdate, 'dd-mon' ) = to_char ( person.dob, 'dd-mon' ) --dob matched today
or --leap logic
(
    -- they are a leapling and it's march 1st --
    to_char ( person.dob, 'dd-mon' ) = '29-FEB' and to_char ( sysdate, 'dd-mon' ) = '01-MAR'
    -- and it's not a leap year --
    and not -- check it's (not) a leap year
    (
        --if divisible by 100, it should be divisible by 400
        ( mod ( extract ( year from sysdate ), 400 ) = 0 )
        or
        --not divisible by 100, but divisible by 4
        ( mod ( extract ( year from sysdate ), 4 ) = 0 and mod ( extract ( year from sysdate ), 100 ) <> 0 )
    )
)
;
---------------------
--not leap year logic
not
(   --if divisible by 100, it should be divisible by 400
    mod ( extract ( year from sysdate ), 400 ) = 0
    or
    --not divisible by 100, but divisible by 4
    ( mod ( extract ( year from sysdate ), 4 ) = 0 and mod ( extract ( year from sysdate ), 100 ) <> 0 )
)
---------------------
--cte to store leap years
with t as (select 1900+rownum-1 as yr from dual connect by rownum <= 200)
select
    yr
    ,case
        --1st WHEN: mod(yr,100) = 0 here is optional since always true for 100 if true for 400.
        when mod(yr,400)=0 and mod(yr,100) = 0 then 'leap year'
        when mod(yr,4) = 0 and mod(yr,100) != 0 then 'leap year'
        else null
    end as leap
from t
order by yr
;