Unexpected Date Results in SQLite Leap Year Calculations

Gregorian Calendar Anomalies and SQLite Date Arithmetic

Understanding Date Handling Around Non-Leap Years (e.g., 1900)

The core issue revolves around SQLite’s adherence to the Gregorian calendar rules when performing date arithmetic, particularly for years that are exceptions to the standard leap year calculation. SQLite follows the rule that a year divisible by 100 is not a leap year unless it is also divisible by 400. This means 1900 is not a leap year, but 2000 is. The problem arises when date calculations span these exceptional years, leading to seemingly inconsistent results. For example:

1. Adding 4 years to 1896-02-29 (a valid leap date) results in 1900-03-01 instead of an invalid 1900-02-29.
2. Adding another 4 years to 1900-03-01 yields 1904-03-01, not 1904-02-29, because the intermediate step (1900-03-01) has already shifted the month/day context.

This behavior is not a bug but a consequence of how SQLite’s date() function processes modifiers left to right, adjusting dates incrementally to maintain validity at each step. When a modifier (e.g., +4 years) results in an invalid date (e.g., 1900-02-29), SQLite automatically normalizes it to the next valid date (1900-03-01). Subsequent operations build on this normalized date, not the original invalid one.

Order of Operations and Modifier Precedence in Date Calculations

SQLite’s date() function applies modifiers in strict left-to-right order. This design choice ensures that each intermediate result is a valid date, but it can lead to counterintuitive outcomes when chaining operations that depend on calendar context. For example:

SELECT 
  date('1896-02-29', '+4 years') AS "Step 1: 1900-03-01",
  date('1900-03-01', '+4 years') AS "Step 2: 1904-03-01";

Here, the first +4 years modifier produces 1900-03-01 because 1900-02-29 is invalid. The second +4 years operates on 1900-03-01, resulting in 1904-03-01. This differs from naively adding 8 years to 1896-02-29, which would skip the invalid intermediate step:

SELECT date('1896-02-29', '+8 years'); -- Result: 1904-02-29

The discrepancy arises because the single +8 years modifier avoids the intermediate normalization to March. This highlights the importance of modifier order and the absence of "lookahead" logic in SQLite’s date arithmetic.

Resolving Date Propagation Errors in Recursive CTEs

Recursive Common Table Expressions (CTEs) that increment dates by fixed intervals (e.g., 4-year increments) will propagate normalization errors if the starting date is near a month boundary affected by leap year exceptions. Consider the original recursive CTE:

WITH dates AS (
  SELECT DATE('1892-02-25') AS d
  UNION ALL
  SELECT DATE(d, '+4 years') FROM dates
  WHERE CAST(STRFTIME('%Y', DATE(d, '+4 years')) AS INT) <= 2026
)
SELECT d FROM dates;

For the year 1900, adding 4 years to 1896-02-25 produces 1900-02-25. However, adding days to this date before applying the year increment (as in the original query) can lead to invalid intermediate dates. For example:

DATE('1896-02-28', '+4 days', '+4 years') -- Evaluated as:
→ DATE('1896-03-03', '+4 years')          -- Intermediate step
→ 1900-03-03

If the intent was to reach 1900-02-29 (invalid), SQLite instead produces 1900-03-03. This "drift" accumulates in recursive CTEs, causing dates to progressively shift away from the intended February context.

Diagnosing and Correcting Date Arithmetic Issues

Step 1: Validate Intermediate Dates in Modifier Chains

Break down chained modifiers into individual steps to inspect intermediate results. For example:

-- Problematic query
SELECT DATE('1896-02-29', '+4 years', '+1 day');

-- Breakdown:
SELECT 
  DATE('1896-02-29', '+4 years') AS intermediate_date, -- 1900-03-01
  DATE('1900-03-01', '+1 day') AS final_date;          -- 1900-03-02

This reveals that the +1 day operates on 1900-03-01, not the (invalid) 1900-02-29. To avoid this, reverse the modifier order if logically appropriate:

SELECT DATE('1896-02-29', '+1 day', '+4 years'); -- 1900-03-02 (same result)

Step 2: Use Conditional Logic for Leap Year Adjustments

Explicitly check for leap years when performing year increments involving February dates:

WITH dates AS (
  SELECT '1892-02-29' AS d
  UNION ALL
  SELECT 
    CASE 
      WHEN STRFTIME('%m-%d', d) = '02-29' AND 
           (CAST(STRFTIME('%Y', d) + 4 AS INT) % 100 = 0) AND
           (CAST(STRFTIME('%Y', d) + 4 AS INT) % 400 != 0) 
      THEN DATE(d, '+4 years', '-1 day') 
      ELSE DATE(d, '+4 years') 
    END
  FROM dates
  WHERE CAST(STRFTIME('%Y', d) AS INT) + 4 <= 2026
)
SELECT d FROM dates;

This logic subtracts a day when adding 4 years to a leap date would land on a non-leap year (e.g., 1900), preserving the February context.

Step 3: Avoid Ambiguous Date Constructs in Recursive CTEs

When generating dates recursively, anchor the CTE to a stable date format that avoids month/day ambiguities. For example, use the first day of the month:

WITH dates AS (
  SELECT DATE('1892-02-01') AS d
  UNION ALL
  SELECT DATE(d, '+4 years') FROM dates
  WHERE CAST(STRFTIME('%Y', d) AS INT) + 4 <= 2026
)
SELECT 
  d,
  DATE(d, '+1 month', '-1 day') AS month_end_date
FROM dates;

This generates month-end dates without relying on unstable day-of-month values. For February, this correctly yields 1892-02-29, 1896-02-29, 1900-02-28, etc.

Final Recommendations

1. Explicit Leap Year Handling: Use STRFTIME('%j', d) (day of year) or conditional checks to detect leap years before performing critical date arithmetic.
2. Modifier Order: Always apply year increments after day/month adjustments if the goal is to preserve the original day/month context.
3. Validation Tools: Use the ISDATE() user-defined function (via extension) or subqueries to verify date validity during incremental calculations.

By understanding SQLite’s strict adherence to left-to-right modifier processing and Gregorian rules, developers can anticipate and mitigate date arithmetic anomalies around non-leap years like 1900.