How can I sort dates stored as ISO text in SQLite?

For a column containing valid dates all written as zero-padded YYYY-MM-DD text, sort the column directly:

SELECT event_name, event_date
FROM events
ORDER BY event_date ASC;

ASC means ascending, so the earliest date comes first. With this specific format, the year comes first, then month, then day; comparing the text in that order gives chronological order. SQLite permits dates to be stored as text, but does not have a dedicated date storage class. [S1]

For newest first, use ORDER BY event_date DESC. This assumes consistently formatted date-only values, rather than timestamps or a mixture of formats.

Suppose the events table contains these illustrative rows:

event_name | event_date
Review     | 2024-11-03
Launch     | 2023-02-14
Planning   | 2024-01-09
Update     | 2023-12-28

Run:

SELECT event_name, event_date
FROM events
ORDER BY event_date ASC;

The expected order is:

1. Launch — 2023-02-14
2. Update — 2023-12-28
3. Planning — 2024-01-09
4. Review — 2024-11-03

Every value has a zero-padded month and day, so text ordering follows the year-month-day sequence and gives chronological order. [S1]

Text sorting compares the stored text, not the intended meaning of each value. For example, 2024-2-05 does not use the fixed-width month pattern of YYYY-MM-DD, and 02/05/2024 puts components in a different order. The chronological guarantee applies to valid, consistently formatted, zero-padded YYYY-MM-DD text—not arbitrary mixtures. [S1]

Inspect values that do not match your intended format. If possible, standardize them when writing or cleaning the data, then sort the standardized column. ORDER BY alone does not repair inconsistent text.

SQLite supports date/time values represented as ISO-8601 text, Julian day numbers, or Unix timestamps. Its date/time functions work with supported time-value formats, and date() returns text formatted as YYYY-MM-DD. [S2] That does not mean arbitrary mixed-format text will sort chronologically, or that every value in your column is valid or convertible. [S1][S2]

For values accepted as ISO-8601 text, you can use date() to produce a date-formatted result:

SELECT event_name, date(event_date) AS normalized_date
FROM events
ORDER BY normalized_date ASC;

Review the converted results against the original values before relying on them. The available function does not establish that an unknown dataset contains only valid, consistently interpretable dates. For a stable solution, use one representation consistently and sort by that representation.