Back to Blog

Why Your AI's Date Filter Drops October

Asked for orders since February and October, November and December vanished. The column is text. '2026-10-01' is less than '2026-2-01' because the character '1' comes before '2'.

You ask: "How many orders since February 2026?" The AI says 1,400. You know the autumn was busy. October, November and December are not in the result. September is.

SELECT COUNT(*)
FROM orders
WHERE order_date >= '2026-2-01'

order_date is text, stored as 2026-2-01 with no zero on the month. The comparison is alphabetical. At the first character that differs, '1' comes before '2'.

'2026-10-01'  <  '2026-2-01'     -- October sorts before February
'2026-9-01'   >  '2026-2-01'     -- September sorts after February

So "since February" includes September and drops October, November and December. The query is valid. The months it returns are not a range.

Text order is not calendar order

A real date type compares the calendar. A string compares characters. Those agree only when every value has the same shape, year then month then day, each part the same width: 2026-02-01, 2026-10-01.

Drop the zero, or write the month first, and the order breaks in specific places:

  • Unpadded 2026-10-01 sorts before 2026-2-01, so the last three months fall out of a "since February" filter.
  • 9/15/2026 sorts after 12/01/2026, so "latest date" comes back as September.
  • 02/01/2026 cast to a date is February 1 on one database and January 2 on another.

This is not a timestamp compared to midnight, which returns nothing. You get a large, confident count. It is not a date range that stops at midnight on the last day. Whole months are missing, and a month in the middle can still be present.

How to fix it

Look at the type, then parse with a format you have checked.

SELECT order_date,
       to_date(order_date, 'YYYY-FMMM-FMDD') AS parsed
FROM orders
LIMIT 8

If parsed matches the string on those eight rows, filter on it:

SELECT COUNT(*)
FROM orders
WHERE to_date(order_date, 'YYYY-FMMM-FMDD') >= DATE '2026-02-01'
  AND to_date(order_date, 'YYYY-FMMM-FMDD') <  DATE '2027-01-01'

In MySQL the same check is STR_TO_DATE(order_date, '%Y-%c-%e'). For MM/DD/YYYY text, use that mask and read the sample. A wrong mask swaps month and day and the count changes again, quietly.

Wrapping the column in to_date will not use an ordinary index on order_date. Fine for a check. For a report you run often, add a real date column.

How to catch it

SELECT order_date
FROM orders
WHERE order_date ~ '^[0-9]{4}-[0-9]-'
   OR order_date ~ '^[0-9]{4}-1[0-2]'
LIMIT 5

Any unpadded month, or a month of 10–12 sitting in a column that also has 2026-2-01, will break a string comparison. Then run the AI's filter and a parsed filter and compare the counts. A gap that is mostly October, November and December is this bug.

The prompt that heads this off

Before you filter or sort a date column, check its type. If it is text,
show me eight raw values next to the parsed dates and the format you used.
Do not compare the strings. Months 10, 11 and 12 sort before an unpadded
February. Slash dates need an explicit month/day mask.

The short version

'2026-10-01' < '2026-2-01' is true for text and false for dates. Parse the column, check the parse against the raw strings, and filter the dates.


Read-only MCP gateway: mcpserver.design.

Related reading: