SQL Date Filtering Returning Wrong Ranges: BETWEEN, Truncation, and Timezone Traps

You've written what looks like a perfectly valid SQL query.

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

The query executes successfully.

But something isn't right.

You notice:

• Missing records
• Incorrect totals
• Reports that don't match expectations
• End-of-day transactions disappearing
• Different results between environments

The syntax isn't the problem.

The issue usually lies in how SQL interprets dates, timestamps, time zones, and range boundaries.

Date filtering is one of the most common sources of subtle bugs in production databases. A query that appears correct can silently exclude valid records or perform poorly because of seemingly minor implementation details.

Understanding these pitfalls is essential for writing accurate and efficient SQL.

What You Will Learn From This Article

After reading this guide, you'll understand:

• Why BETWEEN causes unexpected results.
• Timestamp versus date comparisons.
• Time zone pitfalls.
• Date truncation issues.
• Index-friendly filtering techniques.
• Best practices for production SQL queries.

Why Date Filtering Is Harder Than It Looks

Most database columns aren't stored as simple dates.

Instead,

they often contain:

• Date
• DateTime
• Timestamp
• Timestamp with Time Zone

That extra time information changes how filters behave.

Problem #1

Using BETWEEN With DateTime Columns

Suppose your query is:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31';

If OrderDate includes time values,

the ending date becomes:

2025-01-31 00:00:00

Transactions later that day may be excluded.

Solution

Prefer half-open ranges:

SELECT *
FROM Orders
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2025-02-01';

This pattern reliably includes the full month regardless of timestamp precision.

Problem #2

Truncating the Column

Developers sometimes write:

WHERE DATE(OrderDate) = '2025-01-15';

or

WHERE CAST(OrderDate AS DATE) = '2025-01-15';

While correct logically,

these expressions often prevent efficient index usage because the database must evaluate the function for every row before filtering.

Solution

Filter directly on the original column:

WHERE OrderDate >= '2025-01-15'
AND OrderDate < '2025-01-16';

This approach is typically more index-friendly.

Problem #3

Time Zone Confusion

Applications frequently store timestamps in UTC while users view data in local time zones.

For example:

Database:

2025-07-10 22:30 UTC

User:

2025-07-11 04:30 Asia/Dhaka

Filtering by local dates without proper conversion may exclude or duplicate records.

Solution

Establish a consistent strategy for storing timestamps—commonly UTC—and convert values appropriately when filtering or presenting data to users.

Problem #4

Mixing Date and DateTime Values

Suppose one table stores:

DATE

while another stores:

TIMESTAMP

Direct comparisons can produce unexpected behavior.

Solution

Normalize comparisons so both sides represent equivalent temporal values before filtering or joining.

Problem #5

Implicit Type Conversion

Queries such as:

WHERE OrderDate = '01/02/2025';

may be interpreted differently depending on:

• Database engine
• Server locale
• Session settings

Solution

Use unambiguous ISO 8601 date formats whenever possible:

2025-02-01

Problem #6

End-of-Day Assumptions

Many developers assume:

2025-01-31

includes:

2025-01-31 23:59:59

It often does not.

The actual interpretation depends on the database system and data type.

Solution

Use exclusive upper bounds rather than relying on implicit end-of-day behavior.

Problem #7

Daylight Saving Time

Applications serving multiple regions may encounter:

• Missing hours
• Repeated hours
• Offset changes

during daylight saving transitions.

Solution

Store timestamps consistently and perform time zone conversions using the database or application framework rather than manually adjusting offsets.

Problem #8

Comparing Strings Instead of Dates

Sometimes imported data stores dates as text.

Example:

01-12-2025

Lexical comparison is not equivalent to chronological comparison.

Solution

Convert string values into proper date or timestamp types before performing comparisons or sorting.

Problem #9

Ignoring Precision

Modern databases may store:

• Seconds
• Milliseconds
• Microseconds
• Nanoseconds

Two timestamps that appear identical in reports may differ at higher precision.

Solution

Understand the precision supported by your database and design comparisons accordingly.

Database-Specific Considerations

Different SQL databases implement date functions differently.

For example:

• PostgreSQL
• MySQL
• SQL Server
• Oracle
• SQLite

Although the underlying concepts remain similar, syntax and built-in functions vary. Always consult the documentation for your specific database engine before relying on vendor-specific date functions or implicit conversions.

Real-World Example

A retail company generates monthly sales reports using the following filter:

WHERE SaleDate BETWEEN '2025-03-01' AND '2025-03-31'

Management notices that the reported revenue is consistently lower than figures from the point-of-sale system.

Investigation reveals that SaleDate is stored as a timestamp. Transactions occurring throughout March 31 after midnight are excluded because the upper boundary effectively becomes 2025-03-31 00:00:00.

Replacing the filter with a half-open range—

WHERE SaleDate >= '2025-03-01'
AND SaleDate < '2025-04-01'

restores the missing transactions and produces accurate monthly totals while maintaining efficient index usage.

Write Date Filters That Scale

Efficient date filtering benefits both correctness and performance.

Good filters should:

• Use indexes effectively.
• Avoid unnecessary functions.
• Handle timestamps correctly.
• Work across time zones.
• Scale to large datasets.

Performance and correctness often go hand in hand.

Best Practices Checklist

When filtering dates in SQL:

✅ Use half-open date ranges

✅ Store timestamps consistently

✅ Prefer UTC for storage

✅ Convert to local time only when necessary

✅ Avoid wrapping indexed columns in functions

✅ Use ISO 8601 date formats

✅ Validate time zone assumptions

✅ Understand timestamp precision

✅ Test month-end and year-end scenarios

✅ Benchmark queries on production-scale data

Common Mistakes to Avoid

Avoid:

❌ Assuming BETWEEN always includes the full final day

❌ Comparing dates as strings

❌ Ignoring time zones

❌ Truncating indexed columns unnecessarily

❌ Mixing local time and UTC inconsistently

❌ Forgetting timestamp precision

❌ Relying on locale-dependent date formats

Design Date Logic Intentionally

Date handling is a foundational part of database design rather than a minor implementation detail. Consistent storage formats, clear time zone policies, and predictable filtering patterns reduce reporting errors, simplify maintenance, and improve query performance. Teams that establish these standards early are far less likely to encounter subtle production issues as applications scale across regions and time zones.

Thoughtful date handling leads to more reliable analytics and operational reporting.

Reliable Reporting Starts With Reliable Filters

Business dashboards, financial reports, audit logs, and customer activity timelines all depend on accurate date filtering. Small mistakes in boundary conditions or time zone handling can silently affect thousands of records without generating any database errors. By adopting half-open ranges, preserving index efficiency, standardizing timestamp storage, and validating edge cases such as month-end and daylight saving transitions, you can build SQL queries that remain accurate and performant over time.

Reliable date filters are one of the hallmarks of well-engineered database systems.

Frequently Asked Questions (FAQ)

Why does SQL BETWEEN sometimes miss records?

When used with timestamp columns, BETWEEN may interpret the ending date as midnight at the start of that day, excluding records that occur later. Half-open ranges (>= and <) generally provide more predictable results.

Should I store timestamps in UTC?

For applications operating across multiple regions, storing timestamps in UTC is a widely adopted practice because it provides a consistent reference point. Local time conversions can then be applied when displaying or filtering data for users.

Is DATE(column) bad for performance?

Applying functions such as DATE() or CAST() directly to indexed columns may prevent efficient index usage in many database systems. Range-based comparisons are often more performant.

Which date format is safest in SQL?

The ISO 8601 format (YYYY-MM-DD) is generally the safest and least ambiguous choice for date literals because it avoids locale-dependent interpretations.

Wrapping Summary

SQL date filtering can produce misleading results when timestamp precision, BETWEEN boundaries, truncation functions, implicit type conversions, or time zone differences are overlooked. Queries that appear logically correct may silently exclude valid records, reduce index efficiency, or generate inconsistent reports across environments. Understanding how your database stores and compares temporal values is essential for building reliable applications.

By adopting half-open date ranges, storing timestamps consistently, using unambiguous date formats, avoiding unnecessary functions on indexed columns, and carefully managing time zone conversions, you can write SQL queries that are both accurate and scalable. These practices improve reporting quality, enhance query performance, and reduce the likelihood of subtle production bugs that are often difficult to diagnose.