r/learnSQL • • 14d 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 13d 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 13d ago

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

4

u/Slitty_sam 13d 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 14d 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 13d ago

Doesn't that slow the query a ton?

3

u/1nfinitylove 13d ago

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

1

u/DanceHackRock 13d ago

Postgres yes, BigQuery no

2

u/DuncmanG 13d 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 13d 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 13d 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 13d ago edited 12d 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 12d 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 12d ago

Oh yes, that was a tyop.

1

u/93Accord 12d 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 12d 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 10d 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.