Issue Overview: Misalignment Between strftime(‘%W’) and ISO Week Standards

The core issue arises from a discrepancy between the week number returned by SQLite’s strftime('%W') function and the ISO 8601 week numbering standard. Users attempting to derive ISO week numbers for dates around year boundaries (e.g., January 1–3, 2021) observed unexpected results, such as week "00" instead of the expected "53" for ISO week 53 of the preceding year. This misalignment stems from fundamental differences in how SQLite’s %W specifier calculates weeks compared to ISO 8601 rules.

ISO 8601 defines weeks as starting on Monday, with the first week of the year containing at least four days in the new year. For example:

• January 1, 2021 (Friday) belongs to ISO week 53 of 2020.
• January 4, 2021 (Monday) marks the start of ISO week 1 of 2021.

SQLite’s %W, however, calculates weeks differently: it considers weeks to start on Monday but defines week 01 as the first week with at least four days in the new year. This is subtly distinct from ISO 8601, which uses a "the majority of the week’s days" rule. The confusion intensifies when years have 53 weeks (e.g., 2020, a leap year). The user’s test cases explicitly highlight this mismatch:

-- Returns "00" for 2021-01-01 instead of "53"
SELECT strftime('%W', '2021-01-01');

Possible Causes: Mechanism of strftime(‘%W’) vs. ISO 8601 Requirements

1. Divergent Week-Numbering Algorithms

SQLite’s %W follows a simplified week-counting method:

• Week 01 is the first week with four or more days in January.
• Weeks start on Monday (%u=1).

ISO 8601, however, uses a "yearly alignment" rule:

• Week 01 is the first week with at least four days in the new year.
• The year associated with a week is determined by the year in which Thursday of that week falls.

For dates like 2021-01-01 (Friday), %W assigns week 00 because only three days (Friday–Sunday) fall into 2021. ISO 8601 assigns week 53 to 2020 because the Thursday of that week (December 31, 2020) belongs to 2020.

2. Ambiguity in Year Boundaries

The mismatch is most pronounced during the first three days of January:

• If January 1 falls on Friday–Sunday (as in 2021), %W labels these days as week 00, while ISO 8601 assigns them to week 52 or 53 of the previous year.
• SQLite’s %W does not adjust the year contextually, leading to incorrect year-week pairs (e.g., "2020-53" vs. "2021-00").

3. Legacy Code and Missing ISO Specifiers

Prior to SQLite version 3.46.0 (2024-01-18), the lack of direct support for ISO 8601 specifiers (%V for week, %G for year) forced users to rely on %W, which does not comply with ISO standards. Workarounds involving complex date arithmetic were error-prone and often failed edge cases.

Troubleshooting Steps, Solutions & Fixes

1. Upgrade to SQLite 3.46.0+ for Native ISO 8601 Support

The SQLite team introduced %V (ISO week number) and %G (ISO year) in commit e1155d6a. These specifiers align with ISO 8601:

-- Correct ISO week and year for 2021-01-01
SELECT strftime('%G-W%V-%u', '2021-01-01'); -- Returns '2020-W53-5'

Verification Steps:

1. Check SQLite version:

   SELECT sqlite_version();
   

2. If using a version <3.46.0, upgrade the SQLite library or CLI tool.
3. Test with known edge cases:

   WITH dates(d) AS (VALUES
     ('2021-01-01'), ('2021-01-04'), ('2020-12-31'),
     ('2022-01-01'), ('2023-01-01')
   )
   SELECT d, strftime('%G-W%V-%u', d) AS iso_date FROM dates;
   

   Expected output:

   2021-01-01 | 2020-W53-5
   2021-01-04 | 2021-W01-1
   2020-12-31 | 2020-W53-4
   2022-01-01 | 2021-W52-6
   2023-01-01 | 2022-W52-7
   

2. Workarounds for Older SQLite Versions

For environments where upgrading is impossible, compute ISO week numbers using SQL arithmetic:

Step 1: Adjust the Date to Thursday
ISO weeks are determined by the year of the week’s Thursday. Adjust the date to the nearest Thursday to compute the correct year and week:

SELECT 
  date(
    '2021-01-01', 
    CAST((3 - (CAST(strftime('%w', '2021-01-01') AS INTEGER) + 6) % 7) AS TEXT) || ' days'
  ) AS adjusted_date;
-- Returns '2020-12-31' (Thursday of the week containing 2021-01-01)

Step 2: Compute ISO Week and Year
Using the adjusted date, calculate the week number and year:

WITH adjusted AS (
  SELECT 
    date(d, (3 - ((CAST(strftime('%w', d) AS INTEGER) + 6) % 7)) || ' days') AS thursday
  FROM (VALUES ('2021-01-01')) AS dates(d)
)
SELECT 
  strftime('%Y', thursday) AS iso_year,
  (strftime('%j', date(thursday, '-3 days')) + 6) / 7 AS iso_week
FROM adjusted;
-- Returns iso_year=2020, iso_week=53

Limitations:

• Fails for dates before 0000-01-01 due to SQLite’s date range constraints.
• Requires careful handling of leap years and edge cases.

3. Validate Date Strings and Timezone Handling

Incorrect date formats or timezone offsets can lead to unexpected results. Ensure dates are in YYYY-MM-DD format and explicitly handle timezones:

-- Incorrect due to missing leading zeros
SELECT strftime('%W', '2021-1-1'); -- Fails, returns NULL

-- Correct with leading zeros
SELECT strftime('%W', '2021-01-01'); -- Returns '00' (non-ISO)

-- Using UTC to avoid local timezone interference
SELECT strftime('%G-W%V-%u', '2021-01-01', 'utc');

4. Cross-Validate with External Libraries

For critical applications, cross-check SQLite’s results against trusted libraries (e.g., Python’s isocalendar()):

import sqlite3
from datetime import datetime

conn = sqlite3.connect(':memory:')
conn.create_function('py_isoweek', 1, lambda d: datetime.fromisoformat(d).isocalendar()[1])
cursor = conn.execute("SELECT py_isoweek('2021-01-01')")
print(cursor.fetchone()[0])  # Output: 53

5. Educate Teams on ISO 8601 Nuances

Misunderstandings often arise from conflating different week-numbering systems. Key points to emphasize:

• ISO weeks start on Monday.
• The year has 52 or 53 weeks (53 if January 1 is a Thursday or leap year January 1 is a Wednesday).
• Use %V and %G in SQLite ≥3.46.0; avoid %W for ISO purposes.

Final Recommendation

Upgrading to SQLite 3.46.0+ and adopting %V/%G is the simplest, most reliable solution. For legacy systems, implement the Thursday adjustment method with rigorous testing. Always validate results against known dates (e.g., 2021-01-01 = 2020-W53-5) to ensure correctness.