Menu

SQLite strftime(): Format Codes and Examples

Updated on

SQLite strftime(format, time_value, modifier, ...) formats a date or time value as text. The format argument is required; time_value is optional and defaults to now. Modifiers are applied from left to right.

Format codes

Code Result
%d Day of month (01–31)
%e Day of month without a leading zero (1–31)
%f Fractional seconds (SS.SSS)
%F ISO date (YYYY-MM-DD)
%G ISO week-numbering year
%g Two-digit ISO week-numbering year
%H Hour (00–24)
%I Hour on a 12-hour clock (01–12)
%j Day of year (001–366)
%J Fractional Julian day number
%k Hour without a leading zero (0–24)
%l 12-hour clock hour without a leading zero (1–12)
%m Month (01–12)
%M Minute (00–59)
%p / %P Uppercase / lowercase AM or PM
%R 24-hour time (HH:MM)
%s Unix timestamp in seconds
%S Seconds (00–59)
%T 24-hour time (HH:MM:SS)
%U Week of year (00–53); week 01 starts on the first Sunday
%W Week of year (00–53); week 01 starts on the first Monday
%u ISO weekday (1 for Monday through 7 for Sunday)
%V ISO week number (01–53)
%w Weekday (0 for Sunday through 6 for Saturday)
%Y Year (0000–9999)
%% A literal percent sign

This table lists the substitutions documented by SQLite 3.46.0. Older SQLite versions may not support every code; an undefined or unsupported substitution makes strftime() return NULL.

Examples

Format an ISO date and time:

SELECT strftime('%Y-%m-%d %H:%M:%S', '2024-01-02 03:04:05') AS formatted;

formatted
-------------------
2024-01-02 03:04:05

ISO week numbering can differ from the calendar year near New Year’s Day. January 1, 2021 belongs to ISO week 53 of 2020:

SELECT strftime('%G-W%V-%u', '2021-01-01') AS iso_week_date;

iso_week_date
-------------
2020-W53-5

For fractional Unix seconds as text, add the subsec modifier (SQLite 3.42.0 or later):

SELECT strftime('%s', '2024-01-02 03:04:05.678', 'subsec') AS epoch_seconds;

epoch_seconds
--------------
1704164645.678

%J and %s produce numeric values as text. With SQLite 3.42.0 or later, add subsec after the time-value to make %s include fractional seconds; the result remains text. Use unixepoch() with subsec when you need a numeric fractional timestamp, or julianday() for fractional days. These functions also avoid format-conversion costs.

For time-value formats and supported modifiers, see the SQLite date and time documentation.

To compare SQLite’s substitutions with MySQL, MariaDB, PostgreSQL, SQL Server, and Oracle format patterns, see date and time formatting by database.

Advertisement