• Post author:
  • Post category:All / SQL
  • Reading time:1 mins read

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,
case
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

Rating:
5

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.