SQLite date() Function Silently Adjusts Invalid Month Days to Next Month

Issue Overview: date() Function Normalizes Days Beyond Month Limits Without Validation

The SQLite date() function exhibits unexpected behavior when handling dates where the day value exceeds the maximum valid day for a given month. For example, date('2024-04-31') returns 2024-05-01 instead of NULL, effectively rolling over invalid days into the next month. This behavior occurs even when the input day is within the range 29–31 for months that cannot accommodate such days (e.g., February in non-leap years, April, June, September, November). However, days outside the 1–31 range (e.g., 2024-01-32) are rejected and return NULL.

This normalization is inconsistent with strict date validation expectations. Users may assume that date() acts as a validator, rejecting all invalid dates. Instead, SQLite treats certain "out-of-bounds" days as overflows, adjusting the month and year accordingly. For instance:

• 2024-02-30 becomes 2024-03-01 (February 2024 has 29 days).
• 2025-02-29 becomes 2025-03-01 (2025 is not a leap year).
• 2024-04-31 becomes 2024-05-01 (April has 30 days).

This behavior poses risks in applications that rely on SQLite to validate dates. A user inputting 2024-04-31 might assume the date refers to April, but SQLite silently converts it to May 1. This could lead to data integrity issues, incorrect reporting, or logic errors in applications that do not account for this implicit conversion.

The inconsistency arises from SQLite’s design philosophy, which prioritizes flexibility and robustness over strict validation. The date() function parses inputs liberally, normalizing them into valid dates wherever possible. However, this design choice is not immediately obvious from the documentation, leading to confusion.

Possible Causes: Design Philosophy, Validation Simplification, and Time Zone Complexity

1. Robustness Principle (Postel’s Law) in Date Handling

SQLite adheres to Postel’s Law ("Be conservative in what you send, liberal in what you accept"), allowing date inputs to be interpreted flexibly. The date() function normalizes inputs by rolling excess days into subsequent months. This avoids rejecting dates that could be validly represented in alternative forms (e.g., 2024-04-31 as 2024-05-01). However, this approach shifts the burden of strict validation to the user.

2. Simplified Date Validation Logic

SQLite’s date functions avoid complex validation checks for month-day combinations. Instead, they treat days as offsets from the start of the month, wrapping excess days into the next month. For example:

• 2024-02-30 is parsed as "February 1 + 29 days" (which overflows into March).
• 2024-04-31 is parsed as "April 1 + 30 days" (overflowing into May).

This avoids computationally expensive checks for month lengths, leap years, or Gregorian calendar rules during initial parsing. The trade-off is that invalid dates are converted into valid ones, bypassing explicit validation.

3. Ambiguity in Local Time and Daylight Saving Time (DST)

The date() function’s behavior is further complicated by time zone and DST handling. Local timestamps may be invalid or ambiguous due to DST transitions. For example:

• Invalid Times: During the transition to DST, certain local times (e.g., 2024-03-31 02:30:00 in Paris) do not exist.
• Ambiguous Times: When DST ends, times may repeat (e.g., 2024-10-27 02:00:00 in Paris occurs twice).

SQLite does not flag these issues, instead adjusting timestamps based on its internal UTC conversion. This mirrors its handling of month-day overflows: silent normalization without validation.

Troubleshooting Steps, Solutions & Fixes: Validation Strategies and Workarounds

1. Explicit Date Validation via Round-Trip Checking

To enforce strict date validation, compare the input date string to the result of date() with no modifiers. If they differ, the input is invalid:

WITH inputs(input_date) AS (
  VALUES
    ('2024-04-31'),
    ('2024-02-30'),
    ('2025-02-29'),
    ('2024-09-20')  -- Valid date
)
SELECT
  input_date,
  CASE WHEN input_date = date(input_date) THEN 1 ELSE 0 END AS is_valid
FROM inputs;

Output:

input_date    | is_valid
--------------|---------
2024-04-31    | 0
2024-02-30    | 0
2025-02-29    | 0
2024-09-20    | 1

This method flags dates normalized by SQLite as invalid.

2. Application-Level Date Validation

Validate dates before inserting them into the database using application logic. For example, in Python:

from datetime import datetime

def validate_date(date_str):
    try:
        parsed_date = datetime.fromisoformat(date_str)
        return parsed_date.strftime("%Y-%m-%d") == date_str
    except ValueError:
        return False

print(validate_date("2024-04-31"))  # False
print(validate_date("2024-09-20"))  # True

3. SQLite User-Defined Functions (UDFs) for Strict Parsing

Create a UDF to validate dates without normalization. Use SQLite’s C API or scripting extensions (e.g., Python’s sqlite3 module):

import sqlite3
from datetime import datetime

def strict_date(date_str):
    try:
        datetime.fromisoformat(date_str)
        return date_str
    except ValueError:
        return None

conn = sqlite3.connect(":memory:")
conn.create_function("strict_date", 1, strict_date)

cursor = conn.execute("SELECT strict_date('2024-04-31')")
print(cursor.fetchone())  # (None,)

4. Time Zone and DST Handling

For local timestamps, handle DST transitions explicitly:

• Store dates in UTC.
• Use the unixepoch modifier to work with UNIX timestamps:

  SELECT datetime(1711843200, 'unixepoch', 'localtime');
  

• Avoid relying on SQLite for DST-aware calculations; use application logic or time zone databases (e.g., pytz in Python).

5. Documentation and Awareness

• Update Application Documentation: Clearly state that SQLite normalizes dates and does not perform strict validation.
• Monitor for Future SQLite Updates: The SQLite team may enhance date validation in future releases. Track the SQLite Timeline for changes.

By combining explicit validation, application-level checks, and UDFs, developers can mitigate the risks of silent date normalization. Understanding SQLite’s design choices and limitations is key to avoiding data integrity issues.