Menu
CoddyTech

Read Dates Explicitly

Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 6 of 22.

Our order fixture stores ISO dates as text. Convert ordered_on to date before calendar operations. A typed literal such as DATE '2025-01-01' makes the intended type explicit.

Input table orders:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"channel":"web","items":2}
SELECT id, ordered_on::date AS order_date FROM orders WHERE ordered_on::date < DATE '2025-02-01' ORDER BY id;

The query returns January orders 11 and 12. A date represents a calendar day without a time or time zone. Explicit conversion avoids relying on accidental text ordering.

Cast date text with ::date and use typed DATE literals for calendar comparisons.

challenge icon

Challenge

Medium

Return id and order_date for orders on or after 2025-02-03. Convert ordered_on to date and order by id.

The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.

Try it yourself

-- Write your SQL here.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in SQL Foundations

Practice on your own: PostgreSQL playground