Which Date Functions Matter in Interviews?

Interviews usually test three families. First, extracting parts of a date: the year, the month, the day of the week. Second, truncating a timestamp to a period — to the month, the quarter, the week. Third, arithmetic: adding days, and measuring the gap between two dates.

The dialects diverge sharply here. SQLite uses strftime with a format string for both extraction and truncation: strftime('%Y', d) gives the year, strftime('%Y-%m', d) gives the month, and strftime('%Y-01-01', d) truncates to the start of the year. MySQL has the DATE, YEAR, MONTH and EXTRACT functions plus DATE_FORMAT for formatting, and LAST_DAY for month ends. PostgreSQL has EXTRACT, date_trunc, to_char and an interval type. SQL Server has DATEPART, DATETRUNC from version 2022, and DATEADD for arithmetic. Oracle has EXTRACT, TRUNC for date truncation and ADD_MONTHS.

Three portability notes. NOW(), CURDATE() and GETDATE() are all different; the standard spelling of the current timestamp is CURRENT_TIMESTAMP, and in SQLite the equivalent is datetime('now'). And the interval arithmetic is the least portable area of all: d1 - d2 gives a number of days in SQLite, an interval in PostgreSQL, and a datetime in MySQL, so any difference calculation needs a dialect-specific expression.

One more decision that matters more than the function choice: whether to store dates as native date types or as text. Native types give you correct comparison, arithmetic and indexing. Text in ISO-8601 format compares correctly lexicographically, which is why it often works, but it silently breaks the moment someone writes '2026-3-7' instead of '2026-03-07' — and it gives you no date functions at all.

Extract a year-month key with strftime, group on it, and the report writes itself. Keeping the year in the key matters — grouping on the month alone lumps January 2026 together with January 2027.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  order_date TEXT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, order_date, amount) VALUES
  (1, '2026-01-14', 120.00),
  (2, '2026-01-31',  80.00),
  (3, '2026-02-03', 240.00),
  (4, '2026-02-18',  60.00),
  (5, '2026-03-07', 310.00);

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS order_count,
       ROUND(SUM(amount), 2) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

Three rows come back: 2026-01 with 2 orders and 200.00 of revenue, 2026-02 with 2 orders and 300.00, and 2026-03 with 1 order and 310.00.

Two details behind the query. The %Y-%m format produces a zero-padded month, so the keys sort lexicographically in date order — grouping on %m alone would sort as 01, 02, 03, 10, 11, 12, which happens to work but only because months are zero-padded; a month name would sort alphabetically and put April first. And strftime returns NULL for a value it cannot parse, so a malformed date produces a NULL group rather than an error — check for it if the data comes from outside.

The dialect note: on PostgreSQL the equivalent is date_trunc('month', order_date) and on MySQL it is DATE_FORMAT(order_date, '%Y-%m'). SQLite's strftime syntax follows the C library conventions, so %Y for year, %m for month, %d for day and %Y-%m-%d' for a full date. The %m and %d are zero-padded while %-m and %-d drop the padding on some builds.

One performance note worth making: the strftime call wraps the column, so an index on order_date cannot be used to satisfy the grouping. If monthly reporting is a hot path, add a stored generated column that holds the month key, index that, and group on it instead.