## 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.