Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Calculate Future Thanksgiving Dates

Jim_KnicelyJim_Knicely Administrator
edited November 2018 in Tips from the Team

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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.