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

Week numbers: %U, %W, and %V

These formats use different rules: %U starts week 01 on the first Sunday of the calendar year, while %W starts week 01 on the first Monday. Dates before that first Sunday or Monday are in week 00. %V follows ISO 8601 weeks (Monday first, week 01 contains the year’s first Thursday).

Compare the results around New Year’s Day:

WITH dates(d) AS (
  VALUES ('2023-01-01'), ('2023-01-02'), ('2021-01-01')
)
SELECT d AS calendar_date,
       strftime('%U', d) AS sunday_week,
       strftime('%W', d) AS monday_week,
       strftime('%G-W%V', d) AS iso_week
FROM dates;

calendar_date  sunday_week  monday_week  iso_week
-------------  -----------  -----------  --------
2023-01-01     01           00           2022-W52
2023-01-02     01           01           2023-W01
2021-01-01     00           00           2020-W53

Use %G-W%V for an ISO week-year, not %Y-W%V. The calendar year (%Y) can differ from the ISO week-numbering year (%G) at the start or end of a year. These examples require a SQLite version that supports %G, %U, and %V (documented in SQLite 3.46.0).

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.

Try the SQLite example online

Run and edit the SQL examples from this page in the SQLite Playground. It uses a real SQLite database in your browser, so you can test the query without installing SQLite.