Is This a Leap Year?

Posted April 16, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

Calendar February 29 for Leap Year
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 a 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/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/LAST_DAY.htm https://en.wikipedia.org/wiki/Leap_year

Have fun!