
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!