SQL: Why Dates Stored as Text Filter Wrong

The symptom

An export from another system stored Ecommerce Orders' order dates as text in the US format, MM/DD/YYYY. The bench rebuilds that column with strftime. You ask for the orders placed since November 1, 2025 with where order_date >= '11/01/2025' and get 581 orders. There are 211.

Why it happens

Text compares one character at a time, from the left, so the month decides first and the year last. '11/03/2024' is greater than '11/01/2025' because 3 beats 1 in the fifth character, and the year is never reached. So the filter also takes November 2 to December 31 of 2023 (189 orders) and of 2024 (181 orders). November 1 itself is left out in both years: '11/01/2023' and '11/01/2025' differ only in the year, and the earlier year is the smaller text.

The fix

Turn the text into a date with strptime, naming the format it was written in, and compare it to a real date:

with imported as (
  select order_id,
         strftime(ordered_at, '%m/%d/%Y') as order_date
  from orders
)
select count(*) as orders
from imported
where order_date >= '11/01/2025'

with imported as (
  select order_id,
         strftime(ordered_at, '%m/%d/%Y') as order_date
  from orders
)
select count(*) as orders
from imported
where strptime(order_date, '%m/%d/%Y') >= date '2025-11-01'

strptime returns a TIMESTAMP; cast it with ::date when you want a plain date column. A value that doesn't fit the format stops the query, with Could not parse string "13/45/2025" according to format specifier. try_strptime returns NULL for those instead, so you can find and count them.

Better: fix the column once

Parsing in every query is easy to forget. Convert the column when the data arrives, and every later query compares real dates:

with imported as (
  select order_id,
         strftime(ordered_at, '%m/%d/%Y') as order_date
  from orders
),
cleaned as (
  select order_id,
         strptime(order_date, '%m/%d/%Y')::date as order_date
  from imported
)
select count(*) as orders
from cleaned
where order_date >= date '2025-11-01'

DuckDB won't compare the text column to a real date without a cast: order_date >= date '2025-11-01' on the text column fails with Cannot compare values of type VARCHAR and type DATE - an explicit cast is required. That error is the safer outcome; the quoted string version runs and is wrong.

Learn more

• Practice question · Parse each school's text dates in its own format
• Lesson 8.8 · Creating a table
• Why numbers stored as text sort wrong
• Reference · CAST and type conversion
• Reference · Date functions