SQL Topic
Date Functions
SQLite's date toolkit is small: strftime for formatting, julianday for day differences, date for arithmetic. Most interview date problems are really date arithmetic: 'the day after first login', 'gap in days between orders'.
When is Date Functions used?
Comparing dates across rows, computing gaps between events, extracting month/year for grouping, and checking next-day activity.
Core syntax
julianday(a) - julianday(b) gives the day difference; strftime('%Y-%m', col) truncates a date to month.Common mistakes
- Using a dialect's DATE_TRUNC / DATE_FORMAT, which SQLite does not have.
- Forgetting that strftime returns TEXT and needs CAST for arithmetic.
- Comparing dates as strings without a consistent format.
Practice Date Functions questions
Work through them in order — each question builds on the last.