📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 9 of 12

Most business questions are about time: this month, last quarter, overdue by 30 days. Date handling is also where SQL dialects differ most, so this lesson shows SQLite (the practice database) with equivalents for the big two.

Store dates as dates

SQLite stores dates as text in ISO format YYYY-MM-DD, which sorts and compares correctly. Other databases have real DATE types. Never store ’04/10/2026′ text; it can’t be compared or sorted reliably.

Date ranges: half-open is safest

SELECT COUNT(*) FROM orders
WHERE order_date >= '2026-08-01' AND order_date < '2026-09-01';

“From the 1st, up to but not including the next month’s 1st” works whether the column holds dates or date-times. BETWEEN ‘2026-08-01’ AND ‘2026-08-31’ misses times on the 31st.

Pulling parts out

Adding days and differences

SELECT order_id, order_date, date(order_date, '+30 days') AS due_date FROM orders;    -- SQLite
-- SQL Server: DATEADD(day, 30, order_date)    PostgreSQL: order_date + INTERVAL '30 days'

SELECT julianday(MAX(order_date)) - julianday(MIN(order_date)) AS days FROM orders;  -- SQLite
-- SQL Server: DATEDIFF(day, MIN(order_date), MAX(order_date))   PostgreSQL: MAX(d) - MIN(d)

Month-end in SQLite: date(d, 'start of month', '+1 month', '-1 day').

Indian financial-year quarter

Q1 is April–June. From the month number m: ((m + 8) % 12) / 3 + 1.

SELECT strftime('%Y-%m', order_date) AS month,
       'Q' || ((CAST(strftime('%m', order_date) AS INTEGER) + 8) % 12 / 3 + 1) AS fy_quarter,
       COUNT(*) AS orders
FROM orders GROUP BY month;

FY start year: year - (month < 4), the same trick as in Excel.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.