Calculate Future Thanksgiving Dates: Quick Tip

Jim Knicely authored this tip.

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/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Date-Time/NEXT_DAY.htm

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

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

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

Have fun!