Leap Years in T-SQL: February 29 and Date Math

February 29 doesn't fit a rule that gives every year 365 days. SQL Server handles leap years through calendar-aware functions, while your business still chooses how anniversaries should behave.

Start With a Real Date Value

Use the date type when the question concerns calendar days. It avoids a time component that the calculation doesn't need. Construct test inputs with DATEFROMPARTS or convert an unambiguous date string explicitly.

Keep birthdays separate from timestamps recording when their records were entered. A birthday describes a calendar date. Converting that date across time zones changes the question without improving its accuracy.

I check the input type before reviewing date arithmetic. A character expression can introduce conversion behavior that distracts from the calendar rule. A typed variable makes the intended operation visible.

The examples below use synthetic dates to expose boundaries. I ran each block on SQL Server 2025, and the results quoted here are what it returned. Execute them yourself and compare.

DECLARE @LeapDate date = DATEFROMPARTS(2024, 2, 29);
SELECT @LeapDate AS OriginalDate,
       DATEADD(year, 1, @LeapDate) AS NextYearDate,
       DATEADD(day, 365, @LeapDate) AS After365Days;

The calendar-year addition returns February 28, 2025. That target year has no February 29. SQL Server supplies the last valid day for this adjustment.

An addition of 365 days counts days instead of calendar anniversaries. Sometimes those two operations land on the same date. Here both columns return February 28, 2025. That coincidence doesn't make the rules interchangeable for every starting date.

Detect Leap Years Without Invalid Dates

Construct February's first day, which exists in every valid year. EOMONTH then gives February's final day. A day number of 29 identifies a leap year.

This avoids trying to construct February 29 for a year that lacks it. DATEFROMPARTS raises an error for invalid arguments. A validation query should check validity before constructing a conditional date.

SELECT y.TestYear,
       EOMONTH(DATEFROMPARTS(y.TestYear, 2, 1)) AS FebruaryEnd,
       CASE WHEN DAY(EOMONTH(DATEFROMPARTS(y.TestYear, 2, 1))) = 29
            THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END AS IsLeapYear
FROM (VALUES (1900), (2000), (2024), (2025), (2100)) AS y(TestYear);

The century examples matter as much as ordinary four-year intervals. Divisibility by four alone doesn't cover the Gregorian century rule. The supplied dates let the calendar function perform that work.

Validate incoming years before calling DATEFROMPARTS. Its date range supports years from 1 through 9999. Missing or out-of-range input deserves a defined response from your procedure.

ISDATE answers whether an expression qualifies as datetime under its conversion rules. It doesn't validate every value supported by datetime2. EOMONTH is clearer for this specific February question.

Avoid language-dependent date strings in validation scripts. A session's date format affects those strings. Numeric date parts keep this test focused on the year rather than session settings.

Count Date Boundaries in Leap Years

DATEDIFF counts the specified boundaries crossed between two values. For date-only inputs, day boundaries describe the calendar-day distance. Include February 29 when it lies between the selected dates.

DECLARE @FromDate date = DATEFROMPARTS(2024, 2, 28);
DECLARE @ToDate date = DATEFROMPARTS(2024, 3, 1);
SELECT DATEDIFF(day, @FromDate, @ToDate) AS CalendarDays;
SELECT DATEDIFF(day, DATEFROMPARTS(2025, 2, 28),
                     DATEFROMPARTS(2025, 3, 1)) AS NonleapCalendarDays;

SELECT DATEDIFF(day,
    CONVERT(datetime2(0), '2024-02-28T23:59:00'),
    CONVERT(datetime2(0), '2024-02-29T00:01:00')) AS CrossedDayBoundaries;

The server returned 2 for the 2024 interval and 1 for the 2025 interval. The timestamp query returned 1. Those values follow directly from the selected calendar dates.

The timestamp example crosses midnight despite representing a short elapsed interval. Its day count therefore doesn't describe full elapsed days. Choose a smaller unit when the contract concerns duration.

For long timestamp intervals, DATEDIFF_BIG provides a larger result range. It still counts boundaries in the selected unit. A bigger integer doesn't change the function's meaning.

I ask whether the requested number represents days, anniversaries, or completed periods. Those labels sound similar in conversation. Their SQL rules differ once February exposes the assumptions.

Choose an Anniversary Policy Explicitly

A February 29 birthday has no identical month and day in a nonleap year. One business policy chooses February 28. Another chooses March 1 for the replacement anniversary.

SQL Server's date adjustment provides a result, not authority over that policy. Get the rule from the owner of the process. Record it before computing reminders or eligibility cutoffs.

DECLARE @BirthDate date = DATEFROMPARTS(2000, 2, 29);
DECLARE @TargetYear int = 2025;
IF @TargetYear IS NULL OR @TargetYear NOT BETWEEN 1 AND 9999
    THROW 51030, 'Target year must be between 1 and 9999.', 1;
DECLARE @IsLeap bit = CASE
    WHEN DAY(EOMONTH(DATEFROMPARTS(@TargetYear, 2, 1))) = 29 THEN 1 ELSE 0 END;
SELECT CASE WHEN MONTH(@BirthDate) = 2 AND DAY(@BirthDate) = 29 AND @IsLeap = 0
            THEN DATEFROMPARTS(@TargetYear, 2, 28)
            ELSE DATEFROMPARTS(@TargetYear, MONTH(@BirthDate), DAY(@BirthDate)) END
            AS FebruaryEndPolicy,
       CASE WHEN MONTH(@BirthDate) = 2 AND DAY(@BirthDate) = 29 AND @IsLeap = 0
            THEN DATEFROMPARTS(@TargetYear, 3, 1)
            ELSE DATEFROMPARTS(@TargetYear, MONTH(@BirthDate), DAY(@BirthDate)) END
            AS MarchStartPolicy;

The query shows both candidates so the distinction stays visible. A production procedure should use the approved rule consistently. Don't choose a policy separately in each report.

What anniversary should your process use when February lacks its final extra day? Ask before writing the scheduled reminder. A function can't attend the policy meeting for you.

Keep the Original Date as the Anchor

Repeatedly adding one year to the previous computed anniversary loses information. Once February 29 becomes February 28, later additions preserve the changed day. The original birthday no longer drives the result.

Compute each target-year anniversary from the original date instead. In the next block, the anchored column returns February 29, 2028, while the reverse adjustment stays on February 28, 2024. That rule also makes rerunning a calculation predictable.

DECLARE @Original date = DATEFROMPARTS(2024, 2, 29);
DECLARE @Adjusted date = DATEADD(year, 1, @Original);
SELECT @Original AS OriginalDate,
       @Adjusted AS AdjustedDate,
       DATEADD(year, -1, @Adjusted) AS ReverseAdjustment,
       DATEADD(year, 4, @Original) AS AnchoredFourYearsLater;

The reverse adjustment doesn't recover the removed day. Calendar adjustments aren't always reversible transformations. Keep original facts rather than trying to infer them from adjusted outputs.

Store the target year and policy beside generated anniversary dates when auditability matters. A later policy change can then be applied deliberately. Previously issued reminders remain explainable under their original rule.

Test Leap Years Beyond One February

Include 2024 followed by 2025 in your test set. Also include a century divisible by 400. These cases expose assumptions hidden by ordinary four-year examples.

Test birthdays on February 28 and March 1 as neighboring controls. Their anniversaries shouldn't change under the special February 29 rule. Include NULL input if your source permits missing birth dates.

For leap years, compare calendar boundaries without removing time precision from duration tests. Casting timestamps to date changes the tested question. Keep day-based and instant-based test cases separate.

A report counting completed years needs an anniversary comparison after the year difference. DATEDIFF(year) alone counts year boundaries. It doesn't establish that the selected anniversary has occurred.

Include a test where the anniversary hasn't occurred yet in the selected target year. Compare it with the exact anniversary date. That checks any completed-year calculation around its actual cutoff.

Publish the Rule With the Result

Use leap years as a reason to state date rules clearly. Keep the original input, target year, and chosen policy available. A reviewer should be able to reproduce the generated date.

Let SQL Server handle valid calendar construction and boundary counting. Let the business owner decide the special anniversary behavior. That division keeps both the query and the resulting reminders understandable.

Related reading on this blog: Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022 and DATEDIFF: Accuracy of Various Dateparts.

A leap-day anniversary is not an automatic business policy, it is a calendar result governed by an explicit rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.