How to Convert String Date With Format in SQLite

SQLite does not have a dedicated date storage type. Applications commonly store dates as text, Julian day numbers, or Unix timestamps. SQLite’s date and time functions interpret supported time-values; they do not automatically understand every string that looks like a date. To format a string date, first make sure it uses an input form SQLite recognizes, then choose the output pattern you need.

The examples below use SQLite SQL. strftime() formats a recognized time-value as text; it does not permanently change the value stored in a table. If your source uses a custom layout such as MM-DD-YYYY, convert it explicitly before relying on date comparisons or calculations.

How SQLite interprets date and time values

SQLite date and time functions accept supported time-values, including common ISO-8601 text forms, Julian day numbers, and Unix timestamps when used with the appropriate modifier. A text column can contain any characters, so storing a value in a column named event_date does not guarantee that SQLite can interpret it. Keep the input convention consistent when you control the data source.

Formatting and parsing are related but distinct. strftime() takes a format string followed by a time-value and returns formatted text when that value is understood. If it cannot interpret the input, the result is NULL. That result is useful to check explicitly rather than assuming every row converted.

Format a supported date string with strftime()

Use a supported ISO date as the input and %Y-%m-%d as the output pattern. %Y means a four-digit year, %m a two-digit month, and %d a two-digit day. The output pattern controls punctuation too, so this example uses hyphens throughout.

Run this self-contained Python example. It sends the SQL query to SQLite and prints the returned value.

import sqlite3

connection = sqlite3.connect(":memory:")
value = connection.execute(
    "SELECT strftime('%Y-%m-%d', '2022-12-31')"
).fetchone()[0]
print(value)
connection.close()

2022-12-31

The equivalent SQL expression is SELECT strftime('%Y-%m-%d', '2022-12-31');. In a query, the result is text, which is convenient for display and export. For chronological comparisons, use a consistent sortable representation such as YYYY-MM-DD, or compare values through SQLite’s date/time functions rather than mixing unrelated text layouts.

Format date values stored in a table

Apply the same function to a column when its values use a supported input form. Suppose events contains the input row event_date = '2022-12-31'. This SQLite query formats it as day/month/year: SELECT event_date, strftime('%d/%m/%Y', event_date) AS formatted_date FROM events;. The corresponding result row is 2022-12-31 | 31/12/2022. The query returns one formatted value per row; it does not update the stored column. Use an UPDATE only when you intentionally want to replace stored data, and keep a backup before doing so.

The input assumption matters. If a row contains another layout or unrelated text, formatting can return NULL for that row. During data cleanup, select both the original column and formatted result so that you can identify values that need separate handling before changing or exporting them.

Handle multiple input formats safely

When a column contains more than one layout, identify each format from a reliable source rule, such as an import type or a separate source_format column. Do not infer the date convention solely from a slash or hyphen: 03/04/2024 could mean March 4 or April 3. Replacing separators also cannot reorder month, day, and year.

For a row whose source rule identifies MM-DD-YYYY, SQLite can rearrange the pieces into a supported ISO date before formatting. For example, with event_date = '12-31-2022' and source_format = 'MM-DD-YYYY', use CASE WHEN source_format = 'MM-DD-YYYY' THEN strftime('%Y-%m-%d', substr(event_date, 7, 4) || '-' || substr(event_date, 1, 2) || '-' || substr(event_date, 4, 2)) WHEN source_format = 'YYYY-MM-DD' THEN strftime('%Y-%m-%d', event_date) ELSE NULL END AS normalized_date. The first branch returns 2022-12-31; a row with an unrecognized source_format returns NULL rather than being presented as converted. A shape check can help route rows, but matching character positions alone does not prove that the month and day form a valid calendar date. Validate calendar dates separately when importing untrusted values.

Check results and handle unrecognized values

Inspect the returned value for NULL and retain the source value while investigating. A NULL result means the date function did not produce a formatted value; it is not a replacement date and should not silently be treated as one. Check whitespace, the exact ordering of year, month, and day, and whether a timestamp includes a suffix your conversion handles.

If the values come from an external feed, normalize them at ingestion and record which source format was used. If you cannot determine a row’s format safely, leave it unresolved for data cleanup instead of guessing. strftime() is also useful for display patterns other than ISO order, but changing the output pattern changes only the returned text, not the stored representation. These examples focus on SQLite; other database engines use different date functions and parsing rules.