Issue Overview: Converting ISO Week Dates to Gregorian Dates in SQLite

The core challenge discussed revolves around SQLite’s inability to directly parse ISO 8601 week-based date formats (e.g., 2022-W41-1) into Gregorian calendar dates using its built-in strftime() and date() functions. Users attempting to convert between these formats encounter unexpected NULL results when using week-based date strings as input. This limitation stems from SQLite’s partial implementation of ISO 8601 date parsing, which omits support for week-based representations despite supporting other ISO 8601 formats. The discussion highlights three critical aspects:

1. Input Format Recognition: SQLite’s date/time functions do not recognize YYYY-Www-D or similar week-based formats as valid date strings.
2. Format Specifier Behavior: Misunderstandings around the behavior of strftime() format specifiers like %G, %V, %U, and %W contribute to confusion during conversion attempts.
3. Documentation Ambiguities: Discrepancies between SQLite’s documentation and the actual behavior of week-numbering specifiers (e.g., %U vs. %V) compound the problem.

Possible Causes: Why SQLite Fails to Parse ISO Week Dates

1. Limited ISO 8601 Subset Support

SQLite explicitly supports only a subset of ISO 8601 formats, as documented in Date and Time Functions. Valid formats include:

• YYYY-MM-DD
• YYYY-MM-DDTHH:MM
• YYYY-MM-DDTHH:MM:SS
• YYYY-MM-DDTHH:MM:SS.SSS

Week-based formats (YYYY-Www-D) are excluded. This omission is intentional due to historical design decisions and the complexity of handling week-based calendars. SQLite’s date/time functions rely on the underlying strftime() C library implementation, which traditionally does not support parsing week-based dates.

2. Week Numbering Specifiers Are Output-Only

The strftime() function in SQLite includes format specifiers like %G (ISO year), %V (ISO week number), and %u (ISO weekday) for formatting dates as ISO week strings. However, these specifiers cannot be used in reverse to parse week-based date strings into Julian Day Numbers or Unix timestamps. This asymmetry means:

• strftime('%G-W%V-%u', '2022-10-10') → Outputs 2022-W41-4 (valid).
• strftime('%Y-%m-%d', '2022-W41-1') → Returns NULL (no parsing support).

3. Ambiguity in Week-Numbering Definitions

SQLite’s documentation originally described %U (week number, Sunday-based) as having a range of 00-53, with "week 01 starting on the first Sunday." This description conflicts with observed behavior:

• For dates before the first Sunday of the year, %U returns 00, not -1 or 53.
• Example: strftime('%U', '2024-01-01') (where Jan 1 is a Monday) returns 00, not -1.

This discrepancy arises from differing interpretations of how week numbers align with calendar years. The underlying code uses a Sunday-based week calculation where days before the first Sunday belong to week 0, but the documentation initially misrepresented this behavior.

4. Cultural and Regional Differences in Date Handling

The discussion reveals a broader context: ISO week dates are widely used in European countries for industrial planning, academic scheduling, and statistical reporting (e.g., Dutch public health data). However, SQLite’s user base historically prioritized Gregorian date formats common in North America and other regions. This cultural mismatch explains why week-date parsing was not previously prioritized in SQLite’s development.

Troubleshooting Steps, Solutions & Fixes

1. Workaround for Parsing ISO Week Dates

Since SQLite cannot directly parse YYYY-Www-D strings, use arithmetic operations to compute the Gregorian date from an ISO week date. Below is a step-by-step method:

Step 1: Extract ISO Year, Week, and Weekday

Assume the input is 2022-W41-1 (ISO year=2022, week=41, weekday=1 [Monday]). Split the string into components:

WITH iso_date(iso_year, iso_week, iso_weekday) AS (
  SELECT 2022, 41, 1
)

Step 2: Calculate the First Thursday of the ISO Year

ISO weeks rely on the first week containing the year’s first Thursday. Compute this date using:

SELECT date(iso_year || '-01-04', 'weekday 4') AS first_thursday

This finds the first Thursday (weekday 4) on or after January 4th, which always lies in the first ISO week.

Step 3: Compute the Start of Week 1

Subtract 3 days from the first Thursday to get the Monday of week 1:

SELECT date(first_thursday, '-3 days') AS week1_start

Step 4: Calculate the Target Date

Add (iso_week - 1) * 7 + (iso_weekday - 1) days to week1_start:

SELECT date(week1_start, '+' || ((iso_week - 1) * 7 + (iso_weekday - 1)) || ' days') AS gregorian_date

Full Query:

WITH iso_date(iso_year, iso_week, iso_weekday) AS (
  SELECT 2022, 41, 1
),
first_thursday AS (
  SELECT date(iso_year || '-01-04', 'weekday 4') AS ft FROM iso_date
),
week1_start AS (
  SELECT date(ft, '-3 days') AS ws FROM first_thursday
)
SELECT date(ws, '+' || ((iso_week - 1) * 7 + (iso_weekday - 1)) || ' days') AS gregorian_date
FROM week1_start, iso_date;

Result: 2022-10-10

2. Validating Week Number Calculations

To avoid edge-case errors (e.g., week 53 in some years), validate the computed Gregorian date by converting it back to an ISO week date:

SELECT
  strftime('%G', gregorian_date) AS iso_year,
  strftime('%V', gregorian_date) AS iso_week,
  strftime('%u', gregorian_date) AS iso_weekday
FROM (SELECT '2022-10-10' AS gregorian_date);

Result: 2022|41|1

3. Correcting Misleading Documentation Entries

The documentation for %U was updated to clarify:

%U – Week number (00-53). Weeks begin on Sunday. Days preceding the first Sunday of the year are in week 00.

This aligns with SQLite’s behavior, where strftime('%U', '2024-01-01') returns 00 (Jan 1 is a Monday in 2024).

4. Implementing Custom Functions for ISO Dates

For frequent conversions, extend SQLite with a user-defined function (UDF) written in C:

#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1
#include <time.h>

static void isoToGregorian(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
) {
  const char *iso_str = (const char*)sqlite3_value_text(argv[0]);
  int year, week, weekday;
  if (sscanf(iso_str, "%d-W%d-%d", &year, &week, &weekday) != 3) {
    sqlite3_result_null(context);
    return;
  }

  struct tm tm = {0};
  tm.tm_year = year - 1900;
  tm.tm_mon = 0; // January
  tm.tm_mday = 4; // January 4th
  mktime(&tm); // Normalize tm

  // Adjust to the first Thursday
  while (tm.tm_wday != 4) { // tm_wday: 0=Sunday, ..., 4=Thursday
    tm.tm_mday++;
    mktime(&tm);
  }

  // Jump to Monday of week 1
  tm.tm_mday -= 3;
  mktime(&tm);

  // Add weeks and days
  tm.tm_mday += (week - 1) * 7 + (weekday - 1);
  mktime(&tm);

  char buf[20];
  strftime(buf, sizeof(buf), "%Y-%m-%d", &tm);
  sqlite3_result_text(context, buf, -1, SQLITE_TRANSIENT);
}

int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
) {
  SQLITE_EXTENSION_INIT2(pApi);
  sqlite3_create_function(db, "iso_to_gregorian", 1, SQLITE_UTF8, NULL,
    isoToGregorian, NULL, NULL);
  return SQLITE_OK;
}

Usage:

SELECT iso_to_gregorian('2022-W41-1'); -- Returns '2022-10-10'

5. Leveraging Precomputed Lookup Tables

For applications requiring repeated conversions, create a table mapping ISO week dates to Gregorian dates:

CREATE TABLE iso_gregorian_mapping (
  iso_year INTEGER,
  iso_week INTEGER,
  iso_weekday INTEGER,
  gregorian_date TEXT,
  PRIMARY KEY (iso_year, iso_week, iso_weekday)
);

-- Populate with data using the workaround query

6. Handling Edge Cases and Leap Years

ISO week dates can span Gregorian year boundaries (e.g., ISO 2024-W01 might start in December 2023). Test conversions for:

• Week 53: SELECT iso_to_gregorian('2020-W53-5'); → 2021-01-01
• Leap Years: Ensure February 29th is correctly handled in computations.

7. Alternative Libraries and Tools

For projects requiring full ISO 8601 support, consider:

• SQLite Extensions: Prebuilt extensions like sqlite3-date add broader date parsing.
• Application-Layer Processing: Convert dates outside SQLite using languages like Python or JavaScript, which have robust ISO 8601 libraries.

8. Advocating for Future SQLite Enhancements

While SQLite’s maintainers have not prioritized week-date parsing, users can:

• File a feature request at SQLite’s GitHub Issues.
• Reference the ISO 8601 standard’s YYYY-Www-D format as a critical use case for statistical and international applications.

This guide provides a comprehensive approach to overcoming SQLite’s limitations in handling ISO week dates, combining immediate workarounds with long-term strategies for robust date conversions.