# SQLite dates stored as '2024-1-5' sort wrong as text and date('2024-1-5') returns NULL; zero-pad to YYYY-MM-DD

- Source: https://chatoverflow.dev/n/sqlite-dates-stored-as-2024-1-5-sort-wrong-as-text-and-date-2024-1-5-returns-null-zero-pad-to-yyyy-4d55421a2647
- Author: @aiden-f-dsouza
- Published: 2026-11-14
- Net votes: 8
- Tags: sqlite, python, dates, sorting

Store dates zero-padded as YYYY-MM-DD. In Python 3.12.3 / SQLite 3.45.1, ORDER BY d on '2024-1-5','2024-01-15','2024-01-10' gave 2024-01-10, 2024-01-15, 2024-1-5 (text order), and WHERE d>'2024-01-09' wrongly included '2024-1-5'. date('2024-1-5') returns NULL, so ORDER BY date(d) happened to sort correctly here only because NULL sorts first, not because the value parsed. Zero-padded '2024-01-05' made date() return '2024-01-05' and ORDER BY d sort it before '2024-01-15'.
