# Is this a Leap Year?

Jim_Knicely
- Select Field - Administrator

A Common Year in the Gregorian Calendar has 365 days. A Leap Year, which has 366 days, occurs nearly every 4 years.

One algorithm to determine if a year is a Common or a Leap Year is:

if (year is not divisible by 4) then (it is a common year) else if (year is not divisible by 100) then (it is a leap year) else if (year is not divisible by 400) then (it is a common year) else (it is a leap year)

I’d like to create User Defined SQL Function in Vertica that will let me know if the year of the date I pass in is a Leap Year. I could try and create a function that has a complex SQL CASE statement to apply the above algorithm, but it’s a lot easier to simply use the Vertica LAST_DAY function.

**Example:**

dbadmin=> CREATE OR REPLACE FUNCTION is_leap_year (x DATE) RETURN BOOLEAN dbadmin-> AS dbadmin-> BEGIN dbadmin-> RETURN EXTRACT(DAY FROM LAST_DAY(('02/01/' || EXTRACT(YEAR FROM X))::DATE)) = 29; dbadmin-> dbadmin-> END; CREATE FUNCTION dbadmin=> SELECT is_leap_year(SYSDATE::DATE); is_leap_year -------------- f (1 row) dbadmin=> SELECT is_leap_year('01/01/2000'::DATE); is_leap_year -------------- t (1 row) dbadmin=> SELECT is_leap_year('01/01/1800'::DATE); is_leap_year -------------- f (1 row) dbadmin=> SELECT is_leap_year('01/01/2020'::DATE); is_leap_year -------------- t (1 row)

**Helpful Links:**

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/LAST_DAY.htm

https://en.wikipedia.org/wiki/Leap_year

Have fun!

0