Format dates in SQL (month, year, weekday)
Reports group by month, week or weekday, so the first job is usually turning a full date into the piece you need. In SQLite that's strftime(format, date), with codes like %Y (year), %m (month), %d (day), %W (week number) and %w (weekday, 0 = Sunday).
Syntax
-- SQLite (this site)
strftime('%Y-%m', d)Try itEdit the query, then Run
| order_date | year_month | year | week_number | weekday |
|---|---|---|---|---|
| 2026-01-01 | 2026-01 | 2026 | 00 | Thu |
| 2026-01-01 | 2026-01 | 2026 | 00 | Thu |
| 2026-01-01 | 2026-01 | 2026 | 00 | Thu |
| 2026-01-02 | 2026-01 | 2026 | 00 | Fri |
| 2026-01-02 | 2026-01 | 2026 | 00 | Fri |
| 2026-01-02 | 2026-01 | 2026 | 00 | Fri |
| 2026-01-03 | 2026-01 | 2026 | 00 | Sat |
| 2026-01-04 | 2026-01 | 2026 | 00 | Sun |
8 rows
In other databases
- Postgres, Snowflake, Oracle:
TO_CHAR(d, 'YYYY-MM'),TO_CHAR(d, 'Dy'); to group,DATE_TRUNC('month', d) - MySQL:
DATE_FORMAT(d, '%Y-%m'),DAYNAME(d) - SQL Server:
FORMAT(d, 'yyyy-MM'),DATENAME(weekday, d) - BigQuery:
FORMAT_DATE('%Y-%m', d); to group,DATE_TRUNC(d, MONTH)
For grouping, truncating (keeping a real date, the 1st of the month) beats formatting to text: it sorts and charts correctly.
Your turn
Which weekday gets the most orders? Show weekday_number (strftime '%w') and orders, most first.
GROUP BY strftime('%w', order_date), ORDER BY the count DESC.
This page on one card
