How to handle 29 February when comparing with last year in Snowflake

29 February exists once every four years, so every comparison with “the same date last year” has to decide what to do with it. DATEADD makes a choice for you. It suits most period totals, but not every report.

What DATEADD does

When you move a date by years or months and the target month is shorter, Snowflake keeps the result inside that month:

SELECT DATEADD(year, -1, '2024-02-29'::DATE) AS FromLeapDay,    -- 2023-02-28
       DATEADD(year,  1, '2024-02-29'::DATE) AS ToNextYear,     -- 2025-02-28
       DATEADD(year, -1, '2025-02-28'::DATE) AS IntoLeapYear;   -- 2024-02-28

For a year-to-date comparison this means:

• As of 29 February 2024, last year’s period ends on 28 February 2023. Both years cover the whole of February.
• As of 28 February 2025, last year’s period ends on 28 February 2024, and 29 February 2024 is left out. Both periods have 59 days, so the comparison is like for like by days. A report that calls the period “January and February”, though, compares two complete months with one month and 28 days.

Month-end reports

When the reference date is the last day of its month, compare with the last day of the same month last year:

WITH Params AS (
  SELECT '2025-02-28'::DATE AS AsOf
)
SELECT AsOf,
       IFF(AsOf = LAST_DAY(AsOf),
           LAST_DAY(DATEADD(year, -1, AsOf)),
           DATEADD(year, -1, AsOf)) AS AsOfLastYear
FROM Params;

Only 28 February in the year after a leap year changes. Use AsOfLastYear as the end of last year’s period in the YTD comparison.

Daily comparisons

Charts that put each day next to the same day last year usually join on DATEADD. Some daily totals around the end of February:

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2023-02-27', 10), ('2023-02-28', 20), ('2023-03-01', 30),
  ('2024-02-27', 11), ('2024-02-28', 22), ('2024-02-29', 15),
  ('2024-03-01', 33);

SELECT ty.OrderDate, ty.Amount,
       DATEADD(year, -1, ty.OrderDate) AS LastYearDate,
       ly.Amount AS LastYearAmount
FROM Sales ty
LEFT JOIN Sales ly
  ON ly.OrderDate = DATEADD(year, -1, ty.OrderDate)
WHERE ty.OrderDate BETWEEN '2024-02-27' AND '2024-03-01'
ORDER BY ty.OrderDate;

28 February 2023 appears twice. The LastYearAmount column adds up to 80, while last year sold 60 over the same days. Give 29 February no counterpart:

LEFT JOIN Sales ly
  ON  ly.OrderDate = DATEADD(year, -1, ty.OrderDate)
  AND NOT (MONTH(ty.OrderDate) = 2 AND DAY(ty.OrderDate) = 29)

Now 29 February 2024 has NULL as its last-year amount and the column adds up to 60. A chart shows a gap on that day, which is what happened: 2023 had no 29 February.

The opposite direction has the opposite problem. In 2025, DATEADD(year, -1, ...) never returns 29 February 2024, so last year’s leap day is missing from a daily comparison. Use daily joins for charts only, and compute totals with date ranges, where every day of each period is counted once.

Which rule to choose

• Period totals (YTD, MTD): date ranges built with DATEADD are fine. For month-end reports, end last year’s period with the month-end rule above.
• Daily charts: join on DATEADD and leave 29 February without a counterpart.
• Businesses driven by the weekday: compare with the date 364 days earlier, which maps every day to exactly one day of last year. See how to compare with the same weekday last year.

Whatever rule you choose, state it in the report’s notes. The difference is small, but readers who check the numbers will find it.