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.
SELECT STRFTIME('%d/%m/%Y', '2025-02-14'); -- 14/02/2025
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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') |
-- 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' + 1is2026), but for comparisons across databases writeCAST(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%Vwhere available. - Pad-free months. The codes are zero-padded; to get
2, not02, 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.