Parsing Dates Down to Year, Month, Quarter

Raw timestamps are almost never the grain you want to report at — "revenue per exact second" is meaningless, "revenue per month" is a report. Parsing a date means pulling a coarser unit (year, month, quarter, day-of-week) out of a precise timestamp so rows can be grouped by that unit instead. SQLite stores dates as plain text and exposes strftime() to slice pieces out of that text; PostgreSQL/MySQL/most cloud warehouses instead have a real DATE/TIMESTAMP type with richer, purpose-built functions (DATE_TRUNC rounds down to a unit boundary, EXTRACT pulls out one field, TO_CHAR formats for display) — see Part 12 for the full cross-platform mapping. Date arithmetic (subtracting one date from another to get "days since") is the other half of this: SQLite does it via Julian day numbers (julianday()); most other engines let you subtract dates directly.

SQL · date parsing & arithmetic

SELECT order_date,
       strftime('%Y', order_date)      AS year,
       strftime('%m', order_date)      AS month_number,
       strftime('%Y-%m', order_date)   AS year_month,
       CAST(strftime('%m', order_date) AS INTEGER) AS month_as_int
FROM orders;

-- Date arithmetic: days since the order was placed
SELECT order_date,
       julianday('now') - julianday(order_date) AS days_since_order
FROM orders
ORDER BY order_date DESC;

Round for Display, Not for Math

ROUND(value, decimals) rounds a number to a fixed number of decimal places — simple on its own, but easy to misuse in a multi-step calculation. Rounding early and then re-aggregating compounds a small rounding error across every row it touches; a value rounded to two decimals and then summed a thousand times can drift measurably from the true total. Keep full precision through every intermediate calculation (every JOIN, every SUM/AVG) and round only once, at the very last step, right before the number is displayed to a human. The related trap is integer division: 7 / 2 in most SQL engines silently truncates to 3 if both operands are integers, instead of returning 3.5 — casting one side to a decimal/real type (CAST(7 AS REAL) / 2) forces the exact result.

SQL · round at display time only

SELECT p.category,
       AVG(oi.unit_price)            AS avg_price_unrounded,
       ROUND(AVG(oi.unit_price), 2)  AS avg_price_display
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category;

-- CAST forces exact (non-integer) division:
SELECT 7 / 2                   AS integer_division,       -- 3, silently truncated
       CAST(7 AS REAL) / 2     AS explicit_cast_division;  -- 3.5

Canonicalize Labels with an Explicit Mapping

Real-world text data is rarely consistent: mixed case ("toronto" vs. "Toronto"), stray leading/trailing whitespace, or several spellings of the same underlying value. TRIM() strips whitespace from both ends of a string; UPPER()/LOWER() normalize case so "Toronto" and "TORONTO" compare equal. For anything beyond simple whitespace/case cleanup — folding several raw variants into one canonical label — a CASE WHEN that explicitly maps every known variant to its canonical value is auditable: a reviewer can see the whole mapping in one place and confirm nothing's missing. A chain of ad-hoc REPLACE() calls does the same job but silently misses any variant nobody thought to write a REPLACE for, and there's no single place to review the full list of what's being fixed.

SQL · TRIM/UPPER + CASE WHEN

SELECT DISTINCT city,
       TRIM(city)        AS trimmed,
       UPPER(TRIM(city)) AS standardized
FROM customers;

-- CASE WHEN maps every known raw variant onto one canonical label
SELECT city,
       CASE
           WHEN city IN ('Toronto', 'Ottawa') THEN 'Ontario'
           WHEN city IN ('Montreal')          THEN 'Quebec'
           WHEN city IN ('Vancouver')         THEN 'British Columbia'
           WHEN city IN ('Calgary')           THEN 'Alberta'
           ELSE 'Other'
       END AS region
FROM customers
GROUP BY city;

NULL Is Not Zero, and Not an Empty String

NULL represents "unknown or absent," a distinct third state from an empty string '' or the number 0 — a customer with a NULL phone number never gave one; a customer with '' actively has a blank one on file, which is a different data-quality problem. This distinctness has a sharp practical consequence: comparing anything to NULL with = or != always evaluates to NULL (neither true nor false), never to true — so WHERE phone = NULL silently matches zero rows, always, even for rows where phone genuinely is NULL. Use IS NULL/IS NOT NULL to actually test for it, and COALESCE(column, default) to substitute a fallback value for display or calculation whenever the real value is missing.

SQL · COALESCE + IS NULL

SELECT name, phone, COALESCE(phone, 'not provided') AS phone_display
FROM customers
WHERE phone IS NULL;   -- never "= NULL" -- that always evaluates to NULL, not TRUE

Push Cleaning Upstream

Every cleaning technique above — date parsing, rounding discipline, label mapping, NULL handling — is easy to write once and easy to forget to repeat consistently everywhere it's needed. The fix is architectural, not a new SQL keyword: put the standardization logic in exactly one place (a view, Part 1, or a dbt staging model, Part 10) so every downstream query simply reads already-clean data instead of each analyst repeating a slightly different version of the same CASE WHEN. This is the same idea as a staging layer in a proper ELT pipeline — raw data lands untouched, a staging model cleans it exactly once, and every mart/report built on top of it inherits that cleanup for free.