Calculate Future Thanksgiving Dates

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited November 21 in Vertica Tips

In the United States, Thanksgiving is always celebrated on the fourth Thursday of November. In Canada, Thanksgiving is celebrated the second Monday in October.

So that you don’t miss another holiday at home with mom, make sure to plan ahead and calculate (using SQL) future Thanksgiving dates!

Example:

dbadmin=> SELECT the_year AS "Year",
dbadmin->        (NEXT_DAY(('10-31-' || the_year)::DATE, 'Thursday') + INTERVAL '3 WEEKS')::DATE AS "U.S. Thanksgiving",
dbadmin->        (NEXT_DAY(('09-30-' || the_year)::DATE, 'Monday') + INTERVAL '1 WEEK')::DATE AS "Canadian Thanksgiving"
dbadmin->   FROM (SELECT (ROW_NUMBER() OVER () - 1) + EXTRACT(YEAR FROM SYSDATE) AS the_year FROM columns) foo
dbadmin->  ORDER BY the_year
dbadmin->  LIMIT 13;
Year | U.S. Thanksgiving | Canadian Thanksgiving
-----+-------------------+-----------------------
2018 | 2018-11-22        | 2018-10-08
2019 | 2019-11-28        | 2019-10-14
2020 | 2020-11-26        | 2020-10-12
2021 | 2021-11-25        | 2021-10-11
2022 | 2022-11-24        | 2022-10-10
2023 | 2023-11-23        | 2023-10-09
2024 | 2024-11-28        | 2024-10-14
2025 | 2025-11-27        | 2025-10-13
2026 | 2026-11-26        | 2026-10-12
2027 | 2027-11-25        | 2027-10-11
2028 | 2028-11-23        | 2028-10-09
2029 | 2029-11-22        | 2029-10-08
2030 | 2030-11-28        | 2030-10-14
(13 rows)

Helpful Links:

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Date-Time/NEXT_DAY.htm

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/Date-Time/IntervalOperations.htm

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/ROW_NUMBERAnalytic.htm

https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Date-Time/EXTRACT.htm

Have fun!

Sign In or Register to comment.