How to Filter Date Ranges in SQL Without Mistakes
Make your SQL filters return what you expect
Filtering by date sounds easy until rows go missing or show up twice. The tricky part has more to do with how the values are stored, how time gets bundled in, and how SQL checks them. Even when the query looks right, the results can still be off if the logic doesn’t match how the database handles date and time.
How Date and Datetime Values Are Stored and Compared
Different SQL types carry different behaviors, and how a value is stored plays directly into how it’s compared later. That’s especially true with anything that involves a date or time. When you filter by something that includes a timestamp, even a small gap between formats can cause rows to slip past your filter. Getting a reliable query means knowing how the database treats values behind the scenes before the first row is even returned.
Date Vs Datetime Behavior
A DATE column stores only the calendar part. No time, no timezone, just the year, month, and day. A DATETIME column stores both date and time. Related types such as TIMESTAMP (PostgreSQL, MySQL) or DATETIME2 (SQL Server) do the same, often with micro- or nanosecond resolution depending on the engine. So while both may display something like 2025-06-01, one holds 2025-06-01 and the other holds something like 2025-06-01 00:00:00.000.
The difference becomes real when writing a filter like this:
If paid_on is a DATE, then that will match all records from June 1st. If it’s DATETIME, only rows paid exactly at midnight match. Any row paid at 2025-06-01 08:35:00 won’t be picked up at all. This is where things often start going sideways without much warning. A query looks fine at a glance but returns a partial set because the time part changed the meaning.
What Happens Internally During Comparison
When SQL compares values in a WHERE clause, it checks their types first. If they match, it goes straight to comparing them. If not, the engine decides which one needs to change. This is called implicit conversion, and it happens whether you ask for it or not.
In a query like this:
SQL starts with the literal 2025-06-20 as a date-only value. If the column holds a full timestamp, that gets widened to 2025-06-20 00:00:00 before comparison. That implicit conversion produces a single point in time at midnight, so the filter only matches rows stamped at exactly that moment.
This can be hard to spot at first, especially if your test data happens to include timestamps that match. But as soon as production records start logging values at random times of the day, the filter stops behaving as expected.
A better filter for this case would use a range:
This way, every record from that full date, no matter the time, will be included. The second boundary starts at midnight on the next day, so anything from the 20th is still covered.
How Implicit Casting Works
Implicit casting happens when the database sees two different types in a condition and tries to get them to match. Sometimes it converts the right-hand side, sometimes the left. Which one changes depends on the database engine and the exact expression.
In MySQL, the query:
Casts the string literal '2025-06-01' to a DATETIME with a midnight time component ('2025-06-01 00:00:00') because it’s being compared to a DATETIME column. But if entry_time is stored with millisecond precision and no values are logged exactly at midnight, you won’t get any matches.
To fix this without forcing a function on the column, it helps to write the filter in a way that avoids relying on implicit conversions:
In PostgreSQL, it’s common to see type casting written more explicitly. If the column includes both date and time but you only care about the date part, this works:
You can do the same in SQL Server without casting by using a time-bound range:
This catches everything from June 10 no matter the time. Because the column isn’t wrapped in a function, any index on event_time stays usable, so the engine can seek instead of scanning. Filters like this keep the column untouched and still give you an exact match for the calendar day.
This early type-checking and casting behavior is baked into how SQL engines parse and evaluate queries. When a condition is written, SQL has to figure out the types before it can return any rows. That first step, before any filtering even starts, sets the stage for everything else the query will return.
How to Write Reliable Date Filters Using BETWEEN, >=, and <
After you know how date and datetime values behave under the hood, the next step is getting your filters right. This is where things often go wrong without throwing errors. A query might return fewer rows than expected, or return what looks like a random slice of your data. That usually comes down to how the filter is written and how the engine matches those values. Certain patterns lead to more predictable results, and some are easier to misuse.
Why BETWEEN Can Go Wrong
BETWEEN is one of those clauses that looks readable and tidy, but behaves in ways that aren’t always obvious. The syntax itself includes both endpoints. This works well for plain integers or dates without time, but once datetime values get involved, it’s easy to lose records at the end of the range.
Here’s a query that looks fine:
If started_at is a DATETIME, the string '2025-06-07' gets converted to '2025-06-07 00:00:00'. So the only values from that day it matches are those logged at midnight. Anything from that same day at noon, in the evening, or even a few milliseconds past midnight gets skipped.
You can try adding a time manually, like this:
But that still might not catch values with higher precision, depending on the database. Some engines store microseconds or nanoseconds, and values like 2025-06-07 23:59:59.999999 won’t match unless your filter accounts for that. It also depends on the column’s data type, which might store different levels of precision than what your literal supports.
The safer move is to switch to >= and < and write a range that ends just before the next day starts:
That catches everything from the start of the first day up to, but not including, the start of the eighth. You don’t need to guess about time precision or rely on trailing zeroes to round things out. This format keeps things explicit and accurate.
Performance Notes With Functions in Filters
Sometimes it’s tempting to throw a function into the query to make comparisons easier. Something like this feels readable:
It does the job. It turns a datetime value into a date-only one so it matches directly. But this type of expression blocks the database from using any index on sold_at. That means it has to process every row manually. For smaller tables, that’s not a huge deal. On bigger ones, it slows things down quickly.
Instead of wrapping the column in a function, use a time-bound range that works with the raw value:
This keeps the column side untouched, which lets the database engine use any available indexes. You still get the same matching behavior, but with better performance. It avoids forcing a conversion on every row, and it keeps the query tuned for larger datasets. Using CAST() or DATE() on the right-hand side is fine. Using it on the column is where performance starts to drop. That difference matters more as tables grow and filter conditions get reused across views or stored procedures.
This pattern works across engines, if you’re in MySQL, PostgreSQL, SQL Server, or others. The logic behind it doesn’t depend on vendor quirks. It’s about comparing the stored type as-is and writing a filter that makes sense based on what that value actually holds.
Conclusion
Date filters only work when the logic matches how the database handles stored values. Time precision, type casting, and how comparisons are written all shape the results, often in ways that are easy to miss. Writing filters with open-ended ranges and paying attention to how time is bundled into datetime columns keeps queries from skipping over valid rows. The engine doesn’t guess. It reads exactly what you give it, compares that against what’s stored, and returns only what fits. Getting that match right is what makes the whole filter work.
