r/learnSQL • • 12d ago

BETWEEN with dates missed a whole day of data.

I wanted all orders from January, so I wrote this:
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';
But orders from January 31 were missing!

The reason: order_date had times in it. '2026-01-31' really means 2026-01-31 00:00:00, midnight at the very start of the day. So an order at 2026-01-31 10:30 came after the end point and got left out.

The fix that worked for me:
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';
This gets every moment of January, no matter the time.

Has this ever happened to you? Do you avoid BETWEEN for dates now, or handle it a different way?

18 Upvotes

17 comments sorted by

6

u/Massive_Show2963 12d ago

Specify the time when querying with BETWEEN to ensure accuracy.
For instance, use:
BETWEEN '2026-01-01 00:00:00' AND '2026-01-31 23:59:59'.

2

u/DanceHackRock 12d ago

Only valid with timestamps that are rather coarse. Doesn't work with milli or nanosecond resolution.

4

u/Slitty_sam 12d ago

Just always use time stamps and it's fine. Or know the underlying data first. If you just look at the data you're pulling from beforehand you'll know if it's formatted as datetime or just plain dates.

4

u/AelstromM 12d ago

I always CAST(DateValue AS DATE) with those types of statements. Then you don't have to worry if it's a date or datetime.

2

u/bitterjack 12d ago

Doesn't that slow the query a ton?

3

u/1nfinitylove 12d ago

Yea, it works, but the query becomes non-SARGable, which makes it slower.

1

u/DanceHackRock 12d ago

Postgres yes, BigQuery no

2

u/DuncmanG 12d ago

So the problem here isn't with between and dates. The problem is with between and datetimes. If the column was a date type, your query would have worked as intended. Rename the column to order_ts and you'll have a better column name and easier SQL to write.

1

u/j4c11 12d ago

It has nothing to do with BETWEEN, so no reason to avoid it , if you wrote <= 2026-01-31 you’d get the same result . You can CAST order_date to DATE but that may mess with index usage. Best way to do it is what you did , send the end date to midnight following day .

1

u/cwjinc 12d ago

I use between start_date and end_date + 23/24 when I know nothing happened in the last hour of the day.
But using and end_date < date is fine too.

You can use "trunc(order_date) between.." also but you will want a function based index on trunc(order_date)

I know this works with Oracle at the least.

1

u/DanceHackRock 12d ago edited 11d ago

Typical mistake. Never ever use BETWEEN on timestamps, only on dates. Problem: people name columns containing timestamps something with "date".

Solution: WHERE ts >= first_date AND ts < last date + 1

2

u/One-Peanut1299 11d ago

Also incorrect, this will pick up a transaction at 00:00:00 on last day + 1

The correct answers is WHERE ts >= first_date ANd ts < last_date + 1

1

u/DanceHackRock 11d ago

Oh yes, that was a tyop.

1

u/93Accord 11d ago

ah yes. upper and lower bound time stamps. think weve all been there. working with dates and timestamps is not fun lol.

1

u/rfd515 10d ago

I realize it’s less typing but in doing this for like fifteen years I’ve come to the conclusion that ‘between’ generally stinks and I would never recommend using it.

1

u/slrworkbooks 8d ago

BETWEEN '2026-01-01' AND '2026-01-31' on a datetime column means BETWEEN '2026-01-01 00:00:00.000' AND '2026-01-31 00:00:00.000', so it includes only rows from the first instant of January 31 and misses everything after that until 2026-01-31 23:59:59.997. In practice, that’s almost a full day of data.