Unit 03.00: Types, casting, and the text-date trap

Unit ID: SQL-M03-U01 - Estimated active time: 12-15 minutes Objective: recognise when a comparison is doing something other than what it looks like.

The type decides the comparison

'9' < '10' is false when those are text: string comparison goes character by character, and '9' sorts after '1'. As numbers, 9 < 10 is true. Same symbols, opposite answers.

This is why a date stored as text is a real defect rather than a style preference:

-- If placed_at were VARCHAR, this would compare strings, not moments.
SELECT COUNT(*) FROM orders
WHERE placed_at >= '2026-06-01' AND placed_at < '2026-07-01';
-- 1000

In our database placed_at is a proper TIMESTAMP, so the comparison is chronological and the string literals are cast for you. That is the behaviour you want, and it only happens because the column has the right type.

Casting deliberately

SELECT order_id,
       placed_at,
       CAST(placed_at AS DATE) AS placed_on
FROM orders
WHERE order_id = 501;

Casting in the select list is visible and reviewable. Casting silently inside a filter is where surprises hide - especially when the cast fails on a handful of rows and the engine either errors or, worse, returns NULL for them, which the next unit shows is easy to lose.

Worked example

Counting orders on a single day looks obvious and is easy to get wrong:

-- Wrong when placed_at is a timestamp: only matches exactly midnight
SELECT COUNT(*) FROM orders WHERE placed_at = DATE '2026-06-15';
-- 0

-- Right: a half-open range covering the whole day
SELECT COUNT(*) FROM orders
WHERE placed_at >= '2026-06-15' AND placed_at < '2026-06-16';
-- 33

Zero is a suspicious answer. A count of zero on a day you know has activity is a type problem far more often than it is a data problem.

Practice

Without running it, say what this returns and why:

SELECT COUNT(*) FROM orders WHERE CAST(placed_at AS DATE) = DATE '2026-06-15';

Then say why the half-open range version is still preferable on a large table.

Takeaway

Check the column's type before trusting a comparison. A count of zero where you expected rows is usually a type or boundary problem, not an empty table.

---

Mark complete