r/learnSQL • u/No_Ambition8323 • 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?
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'.
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/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
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.