Dates, Numbers and CASE Expressions

Strings and windows are half the toolbox; the other half is arithmetic and branching. Chapter 18 of the SQL for Beginners series — the last stop before performance — covers SQLite’s date functions, number formatting and its traps, and CASE, the expression that puts if/else into a query. Where an engine disagrees, the differences are named explicitly.

Dates as text: ISO 8601 and strftime

SQLite has no dedicated date type: dates are strings, and they work because the format is fixed — ISO 8601, YYYY-MM-DD, optionally followed by HH:MM:SS. Strings in that shape sort chronologically for free, which is why ORDER BY order_date has always looked right. strftime() takes the format apart, and its first argument is a pattern of its own:

SELECT first_name, signup_date,
       strftime('%Y', signup_date) AS yr,
       strftime('%m', signup_date) AS mo,
       strftime('%w', signup_date) AS dow
FROM customers
ORDER BY signup_date;

Expected output:

first_name | signup_date | yr | mo | dow
Aarav | 2025-11-02 | 2025 | 11 | 0
Priya | 2025-11-15 | 2025 | 11 | 6
Lena | 2025-12-01 | 2025 | 12 | 1
Marc | 2025-12-20 | 2025 | 12 | 6
Wei | 2026-01-12 | 2026 | 01 | 1
Amara | 2026-02-14 | 2026 | 02 | 6

The patterns are %Y four-digit year, %m zero-padded month, %d day, %H:%M:%S time, and %w weekday as 0 for Sunday — so Aarav signed up on a Sunday and Lena on a Monday. date(), datetime() and time() are the shorter siblings: date('2026-01-12 09:30:00') returns 2026-01-12, trimming to the part you asked for.

Date arithmetic in SQLite

Modifiers are written as a string after the value: date(d, '+30 days'), date(d, '-1 month'), date(d, 'weekday 1') (next Monday). Two samples show the whole syntax:

SELECT date('2026-01-12', '+30 days') AS plus_30,
       date('2026-01-12', '-1 month') AS minus_month;

Expected output:

plus_30 | minus_month
2026-02-11 | 2025-12-12

Differences are the awkward part, because SQLite has no DATEDIFF — you convert both dates to a numeric scale with julianday() and subtract. Fix the “today” at a constant so the result is reproducible:

SELECT first_name,
       CAST(julianday('2026-04-01') - julianday(signup_date) AS INTEGER) AS days_since_signup
FROM customers
ORDER BY id;

Expected output:

first_name | days_since_signup
Aarav | 150
Priya | 137
Lena | 121
Marc | 102
Wei | 79
Amara | 46

The same work in MySQL and PostgreSQL

Those engines store real date types, not text, and their function names differ. The mapping for the four jobs this chapter just did:

Two absences are worth remembering: DATEDIFF exists only in MySQL of the three, and SQLite alone offers julianday(). Also mind the clock — date('now') works in UTC while MySQL’s NOW() uses the session time zone and PostgreSQL’s now() carries an offset, so a midnight report can change shape depending on where the server lives.

Numbers: division, rounding and printf

Money arithmetic hides two traps. First, integer division truncates: 899 / 499 is 1, not 1.8, because both operands are integers — make one side a decimal (899.0 / 499, which SQLite prints as 1.80160320641283) or CAST the result. Second, prices with tax need rounding once, at the end, to a stated number of digits:

SELECT name, price, ROUND(price * 1.18, 2) AS with_gst
FROM products
ORDER BY id;

Expected output:

name | price | with_gst
The Silent Patient | 499.0 | 588.82
The Very Hungry Caterpillar | 249.0 | 293.82
Clean Code | 899.0 | 1060.82
Python Crash Course | 749.0 | 883.82
Matilda | 349.0 | 411.82
Robotics for Kids | 649.0 | 765.82

For display, SQLite’s printf() controls width and precision; CAST(price AS INTEGER) does the truncation job when a whole number is really wanted:

SELECT printf('Rs. %.2f', price) AS money,
       printf('%5.0f', price) AS padded,
       CAST(price AS INTEGER) AS as_int
FROM products WHERE id IN (1, 2);

Expected output:

money | padded | as_int
Rs. 499.00 |   499 | 499
Rs. 249.00 |   249 | 249

MySQL’s FORMAT(n, 2) adds thousands separators, PostgreSQL’s TO_CHAR(n, 'FM99990.00') and FORMAT() do the same job with different syntax, and ROUND(x, 2) itself works in all three engines.

CASE: SQL’s if and else

CASE comes in two shapes. The simple form compares one expression against fixed values, like a switch statement; the searched form takes a condition per branch, like a chain of if/elsif. Both end with END:

SELECT id, status,
       CASE status
           WHEN 'paid' THEN 'Ready to fulfil'
           WHEN 'shipped' THEN 'On its way'
           WHEN 'cancelled' THEN 'Refund due'
           ELSE 'Unknown'
       END AS label
FROM orders
ORDER BY id;

Expected output:

id | status | label
1 | paid | Ready to fulfil
2 | shipped | On its way
3 | cancelled | Refund due
4 | shipped | On its way
5 | paid | Ready to fulfil
6 | paid | Ready to fulfil

The searched form handles ranges, which the simple form cannot — price bands read top to bottom, and the first branch that matches wins:

SELECT name, price,
       CASE
           WHEN price >= 800 THEN 'Premium'
           WHEN price >= 400 THEN 'Standard'
           ELSE 'Budget'
       END AS tier
FROM products
ORDER BY price DESC;

Expected output:

name | price | tier
Clean Code | 899.0 | Premium
Python Crash Course | 749.0 | Standard
Robotics for Kids | 649.0 | Standard
The Silent Patient | 499.0 | Standard
Matilda | 349.0 | Budget
The Very Hungry Caterpillar | 249.0 | Budget

Swap the two conditions and the 899 title drops into Standard instead — order is semantics, not style. Both forms work unchanged in SQLite, MySQL and PostgreSQL (MySQL also ships a non-standard IF() shorthand; PostgreSQL has IIF()).

CASE for reporting: conditional aggregation

The payoff is CASE inside an aggregate: one pass over the table can produce every bucket at once, instead of three queries. This is the pivot-table shape:

SELECT SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

Expected output:

paid | shipped | cancelled
3 | 2 | 1

The missing ELSE is the trap. Without it, non-matching rows become NULL rather than 0, and what happens next depends on the function — SUM skips nulls, but COUNT counts them differently:

SELECT COUNT(CASE WHEN price >= 400 THEN 'ok' END) AS big,
       COUNT(CASE WHEN price >= 400 THEN 'ok' ELSE 'no' END) AS all_rows
FROM products;

Expected output:

big | all_rows
4 | 6

Same condition, same table — four rows or six, decided entirely by whether ELSE was written. Get into the habit of always specifying it, even when SUM seems to forgive you.

Common mistakes

• Storing dates in a non-ISO format — DD/MM/YYYY strings sort lexically wrong (“01/02/2026” before “31/01/2026”), so every comparison and index quietly lies; store YYYY-MM-DD.
• Integer division — 899 / 499 returns 1 because both sides are integers; make one operand decimal or cast the result.
• Missing ELSE in a CASE — branches fall through to NULL, which GROUP BY collects into an unlabelled group and COUNT handles differently from SUM.
• Assuming date functions port — DATEDIFF is MySQL-only, julianday() is SQLite-only, EXTRACT and TO_CHAR belong to PostgreSQL; check the engine before pasting.
• Trusting “now” — date('now') is UTC, MySQL’s NOW() is session-local and PostgreSQL’s now() carries a zone; fix the reference date in tests so results reproduce.
• Rounding floats before comparing — binary floating point makes 588.82 an approximation, so compare with a tolerance or keep whole paise as integers.

Practice checklist

1. Group signups by month with strftime('%Y-%m', signup_date) and confirm four groups, with 2025-11 holding two customers.
2. Recompute the days-since-signup query with your own fixed reference date and re-sort by the result.
3. Print the GST query for all six products, predict the 899 line before running it, and check your arithmetic against 1060.82.
4. Rewrite the price-tier query as a simple CASE (hint: bucket with WHEN values, not conditions), then explain why simple CASE cannot express the bands.
5. Break conditional aggregation on purpose: drop the ELSE 0s, switch SUM to COUNT, and watch 3 | 2 | 1 become something else.

What's next?

Every technique in the last six chapters now runs, but “runs” is not “runs fast”. Chapter 19 begins the performance half of the series: how SQLite finds rows, what an index costs, and how to read a query plan in Indexes and Query Performance.