Is a year a leap year?

We could definitely use a standard definition that goes as following:

“A year is a leap year if it is divisible by 4, but century years are not leap years unless they are divisible by 400.”

However, Oracle offers a simpler way of determininf if a year is a leap year.

Let’s present a solution step-by-step:

Step 1: Get the 1st day of a given date (we will be using a SYSDATE in this exercise)

SQL> SELECT TRUNC(SYSDATE,'YEAR') Jan1
  2  FROM dual;

JAN1
---------
01-JAN-16

Step 2: Get the 1st (2nd through 28th would work as well) day of the February:

SQL> SELECT TRUNC(SYSDATE,'YEAR')+31 Feb1
  2  FROM dual;

FEB1
---------
01-FEB-16

Step 3: Get the last day of the February:

SQL> SELECT LAST_DAY(TRUNC(SYSDATE,'YEAR')+31) Feb_Last
  2  FROM dual;

FEB_LAST
---------
29-FEB-16

Step 4: Get the day part of last day of February:

SQL> SELECT TO_CHAR(LAST_DAY(TRUNC(SYSDATE,'YEAR')+31),'DD') Feb_Last
  2  FROM dual;

FEB_LAST
---------
29

Outcome: Since we got 29, it is a leap year.

We can now wrap it up into a function is_leap_year:

CREATE OR REPLACE FUNCTION is_leap_year(p_date DATE) RETURN INTEGER
AS
  v_result INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_result
  FROM dual
  WHERE TO_CHAR(LAST_DAY(TRUNC(p_date,'YEAR')+31),'DD')='29';

  RETURN v_result;
END;
/

Now, we can test this function:

SQL> SELECT 1999+LEVEL AS YEAR, is_leap_year(ADD_MONTHS(DATE'1999-01-01',12*LEVEL)) leap
  2  FROM dual
  3  CONNECT BY LEVEL<=20;

      YEAR       LEAP
---------- ----------
      2000          1
      2001          0
      2002          0
      2003          0
      2004          1
      2005          0
      2006          0
      2007          0
      2008          1
      2009          0
      2010          0

      YEAR       LEAP
---------- ----------
      2011          0
      2012          1
      2013          0
      2014          0
      2015          0
      2016          1
      2017          0
      2018          0
      2019          0

20 rows selected.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements