Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Date and Time Functions

SQL STRFTIME() function

Reference for SQLite STRFTIME(): format codes for year, month, weekday and week number, grouping by month, and the TO_CHAR, DATE_FORMAT and FORMAT equivalents.

STRFTIME(format, time_value, modifier, ...) formats a date-time as text using a template in which codes such as %Y (year) and %m (month) are replaced by parts of the date. It is the Swiss army knife of SQLite date work: it extracts a single part, reformats a date for display, builds YYYY-MM keys for grouping and calculates weekdays and week numbers.

Note the argument order: the format comes first, then the date. The result is always text, even for numeric codes, so cast it when you need a number.

SQL
SELECT STRFTIME('%d/%m/%Y', '2025-02-14');   -- 14/02/2025

Try it

SQL Revenue per month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Format codes on one date

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
STRFTIME(format, time_value [, modifier, ...])
Name Description
format Template text. Codes start with %; everything else is copied as is.
time_value Any value accepted by DATE().
modifier Optional date modifiers.

Common format codes:

Code Meaning
%Y, %m, %d Year (4 digits), month 01-12, day of month 01-31
%H, %M, %S Hour 00-23, minute, second
%f Seconds with milliseconds, SS.SSS
%j Day of the year, 001-366
%w, %u Weekday: %w is 0 (Sunday) to 6; %u is 1 (Monday) to 7
%W Week of the year, weeks starting on Monday, 00-53
%s Unix seconds
%J Julian day number
%F, %T, %R YYYY-MM-DD, HH:MM:SS, HH:MM (SQLite 3.46 and later)
%e, %I, %p, %V, %G Day without zero padding, 12-hour clock hour, AM/PM, ISO week number and ISO week-year (3.46 and later)
%% A literal percent sign

Returns: text, or NULL if the date is invalid or NULL, or if the format contains an unknown code.

Dialect differences

Database Function and codes
SQLite STRFTIME('%Y-%m-%d', d)
PostgreSQL to_char(d, 'YYYY-MM-DD'); date_trunc('month', d) for grouping
MySQL DATE_FORMAT(d, '%Y-%m-%d'); minutes are %i, %M is the month name
SQL Server FORMAT(d, 'yyyy-MM-dd'); CONVERT(varchar(7), d, 120) for YYYY-MM
Oracle TO_CHAR(d, 'YYYY-MM-DD')
SQL
-- PostgreSQL / Oracle
SELECT to_char(ordered_at, 'YYYY-MM') FROM orders;     -- Oracle: TO_CHAR

-- MySQL
SELECT DATE_FORMAT(ordered_at, '%Y-%m') FROM orders;

-- SQL Server
SELECT FORMAT(ordered_at, 'yyyy-MM') FROM orders;

Usage notes

  • Text out. STRFTIME('%Y', d) is '2025'. In arithmetic SQLite converts it ('2025' + 1 is 2026), but for comparisons across databases write CAST(STRFTIME('%Y', d) AS INTEGER).
  • Quarter has no code. Calculate it: (CAST(STRFTIME('%m', d) AS INTEGER) + 2) / 3.
  • Week numbers differ. %W (Monday-based, days before the first Monday are week 00) is not the ISO week; use %V where available.
  • Pad-free months. The codes are zero-padded; to get 2, not 02, cast to integer.
  • Group by the formatted value or a column alias; both work in SQLite. Indexes on the raw date column cannot help a GROUP BY STRFTIME(...).
  • Leading and trailing text in the format is kept, so you can write STRFTIME('Q%Y', d) or '%Y-%m-01' for the first day of the month.