Should BETWEEN never be used for date ranges?

If the column stores a plain date (no time), BETWEEN is safe and readable. If the column is a timestamp/datetime, prefer the ">= start AND < next day" pattern; BETWEEN including the upper bound is what causes silent data loss there.

Can I compare dates as text?

Not recommended. Comparing a date against a string like '2026-01-01' relies on format matching and makes it harder to use an index. Casting to a date type (DATE, CAST, TO_DATE) and comparing as dates is both correct and fast.

Do I still need a range filter when grouping by year or month?

Usually yes. You filter the range with WHERE first, then aggregate by year/month with GROUP BY. Grouping the whole table without a range filter is both slow and pulls in periods you did not want.

How do I compute periods like "last quarter"?

Work out the quarter's start and end and pass them, again, as a half-open interval. In PostgreSQL date_trunc('quarter', ...) makes this easy; on other databases you compute the quarter's first month yourself.

← All postsThe PerSight Team · Last updated: July 23, 2026 · 8 min read

Filtering by Date Range in SQL (Including the BETWEEN Trap)

This month, the last 30 days, last quarter: the safe way to query a date range in SQL. Why BETWEEN misleads once times are involved, and the PostgreSQL, SQL Server, MySQL, and Oracle equivalents.

"Orders this month," "the last 30 days," "last quarter's revenue". Almost every report starts with a date range. It sounds trivial, but if your date column also stores a time, the single most common mistake hides right here. Let's get the safe pattern first, then the functions each of the four databases uses.

Why should I avoid BETWEEN?

Many people write BETWEEN '2026-01-01' AND '2026-01-31' for "January." If the column is a plain date, that is fine. But if the column is a timestamp, an order placed on 31 January at 09:00 falls outside the 2026-01-31 00:00:00 boundary and quietly disappears. Because BETWEEN includes both ends, this trap can run unnoticed for months.

The safe pattern is the "half-open interval": include the start, exclude the end. From the first of the month up to, but not including, the first of the next month:

SELECT *
FROM orders
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2026-02-01';

Correct whether or not a time is stored; this is the pattern to prefer. SQL Server does not accept the DATE literal; write the date as a plain string (2026-01-01) there.

How do I write "the last 30 days"?

When you want a range relative to "today" rather than a fixed date, each database's own "today" and "subtract days" functions come into play. The same query in four dialects:

-- PostgreSQL
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'

-- SQL Server
WHERE order_date >= DATEADD(DAY, -30, CAST(GETDATE() AS date))

-- MySQL
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

-- Oracle
WHERE order_date >= TRUNC(SYSDATE) - 30

Same logic, four dialects.

Current periods like this month or this year

To get the first day of the current month, date_trunc is handy in PostgreSQL; add a month for the upper bound:

-- PostgreSQL: the current month
WHERE order_date >= date_trunc('month', CURRENT_DATE)
  AND order_date <  date_trunc('month', CURRENT_DATE) + INTERVAL '1 month';

SQL Server gives you the last day with EOMONTH; MySQL has LAST_DAY. Even so, the most robust approach is to use the first of the next month as an exclusive bound rather than including the last day; that way the time component never trips you up.

How does the time zone catch me out?

If the column stores UTC but you mean "today" in local time, records near midnight can shift by a day. As a rule, when you compute the range, make sure the bounds and the column are in the same time zone. This detail is the prime suspect behind "why are the numbers a day late?" on daily dashboards.

Frequently asked questions

Should BETWEEN never be used for date ranges?: If the column stores a plain date (no time), BETWEEN is safe and readable. If the column is a timestamp/datetime, prefer the ">= start AND < next day" pattern; BETWEEN including the upper bound is what causes silent data loss there.
Can I compare dates as text?: Not recommended. Comparing a date against a string like '2026-01-01' relies on format matching and makes it harder to use an index. Casting to a date type (DATE, CAST, TO_DATE) and comparing as dates is both correct and fast.
Do I still need a range filter when grouping by year or month?: Usually yes. You filter the range with WHERE first, then aggregate by year/month with GROUP BY. Grouping the whole table without a range filter is both slow and pulls in periods you did not want.
How do I compute periods like "last quarter"?: Work out the quarter's start and end and pass them, again, as a half-open interval. In PostgreSQL date_trunc('quarter', ...) makes this easy; on other databases you compute the quarter's first month yourself.

If keeping track of which date function belongs to which database is not how you want to spend your afternoon, just tell PerSight "orders this month" or "revenue by day for the last 30 days." It builds the query that fits whatever database you happen to be connected to.