Is this a Leap Year?

Jim_Knicely

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!