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:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"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
MediumReturn 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.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
2Dates and Time
Read Dates ExplicitlyDays and IntervalsExtract Calendar PartsMonth BucketsDispatch CalendarPractice on your own: PostgreSQL playground