Home » SQL » Detecting Leap Years In Oracle

Detecting Leap Years In Oracle

Here is the example to determine if any given year is a leap year in Oracle. There are lots of way to get the leap year from any date but some times it becomes complex or typical. The SQL we show next uses the classic definition for leap years.

select to_number(to_char(sysdate,'YYYY')) Year,
when mod(to_number(to_char(sysdate,'YYYY')),400) = 0 then 'Leap Year'
when mod(to_number(to_char(sysdate,'YYYY')),100) = 0 then 'Not Leap Year'
when mod(to_number(to_char(sysdate,'YYYY')),4) = 0 then 'Leap Year'
else 'Not Leap Year'
end as "Leap Year?"
from dual;

For the year of 2009, we can see the results are as expected.
YEAR Leap Year?
---- -------------
2009 Not Leap Year

See also Oracle Date Function Examples

Detecting Leap Year In Oracle

Reviewed by Simpson on

Mar 12