Lesson 10 / 37 · Computing values

Casting and date functions run in SELECT and in WHERE

Functions work in WHERE, to filter, and in SELECT, to display. Both are computed from the same row; WHERE just runs earlier, on every row, and SELECT only on the rows that survive.

The query

SELECT id, order_date,
       strftime(order_date, '%Y-%m') AS month,
       date_diff('day', order_date, DATE '2024-06-01') AS days_before_june,
       CAST(amount AS INTEGER) AS whole_amount
FROM orders
WHERE date_part('month', order_date) >= 3

Try WHERE CAST(amount AS INTEGER) = 20: only order 110 passes, because its 19.50 is rounded before the comparison.

Next: ORDER BY comes late, and ties are arbitrary · All lessons