Sign in Get started

SQLite 3 reference · Date and Time Functions

SQLite STRFTIME() Function

Format a date/time with % codes, such as %Y year, %m month, %d day, %H hour, %W week and %w weekday.

SQLite 3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

strftime(format, time-value, modifier, ...)

Parameters

ParameterDescription
format The template: %Y %m %d %H %M %S, %j day of year, %w weekday (0 = Sunday), %W week, %s Unix seconds.
time-value A date/time: 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS', 'now', a Julian day number, or a Unix time with the 'unixepoch' modifier.
modifiers optional Adjustments applied left to right, such as '+1 day', '-3 months', 'start of month', 'weekday 1', 'localtime', 'unixepoch'.

Return value

Text, or NULL.

Examples

Format datesTry it yourself
SELECT strftime('%d/%m/%Y', '2026-03-09') AS uk_date,
       strftime('%H:%M', '2026-03-09 07:45:00') AS clock,
       strftime('%j', '2026-03-09') AS day_of_year;

Output

uk_date | clock | day_of_year
09/03/2026 | 07:45 | 068
Group by monthTry it yourself
SELECT strftime('%Y-%m', order_date) AS month, count(*) AS orders
FROM orders
GROUP BY month
LIMIT 4;

Output

month | orders
2026-01 | 2
2026-02 | 3
2026-03 | 2
2026-04 | 2
Weekend ordersTry it yourself
SELECT order_date, strftime('%w', order_date) AS weekday
FROM orders
WHERE strftime('%w', order_date) IN ('0', '6');

Output

order_date | weekday
2026-02-14 | 6
2026-02-28 | 6
2026-05-09 | 6
2026-05-30 | 6

In other languages

  • DATE() · Return a date as text in YYYY-MM-DD form, optionally shifted with modifiers like '+7 days' or 'start of month'.
  • DATETIME() · Return a date and time as text in YYYY-MM-DD HH:MM:SS form, optionally shifted with modifiers.
  • UNIXEPOCH() · Return a date/time as a Unix timestamp: whole seconds since 1970-01-01 00:00:00 UTC.
  • SQL Date Functions · tutorial

Was this page helpful?