Page 1 of 1

problem with BETWEEN AND with dates

Posted: Mon Jan 02, 2006 1:13 pm
by jmut
I have this query....

Code: Select all

SELECT date AS pd
FROM registration
WHERE date
     BETWEEN '2005-12-26' AND '2006-01-01'
ORDER BY pd

2005-12-26 till 2006-01-01 is actually a week period (from first day of week till last day including).
I want to catch every date for this period including 26.12 and 01.01

So I guess between should do that as is stated in:
http://dev.mysql.com/doc/refman/4.1/en/ ... ators.html
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max)
Unfortunatelly I get all dates till 31.12 (including) only.

.... only when I say 2006-01-02 I get the 01.01 dates also


Why is that? Do I misunderstand BETWEEN AND operators

Posted: Mon Jan 02, 2006 2:13 pm
by timvw
You understand the concept, but (once again) it appears mysql doesn't behave as it should.. (I believe the standard says that BETWEEN (a, b) is the same as (val >= a) AND (val <= b))

Posted: Tue Jan 03, 2006 1:50 am
by jmut
I see where the problem is....

Task:
Catching all dates between '2005-01-01' AND '2005-01-20'.

WRONG:
I am using BETWEEN AND on datetime column
and used it like this

Code: Select all

BETWEEN '2005-01-01' AND '2005-01-20'

which actually translates internally to

Code: Select all

BETWEEN '2005-01-01 00:00:00' AND '2005-01-20 00:00:00'
And that is why I don't get dates from the last date (I would have catched 2005-01-20 if I had a record with 00:00:00 hours).

CORRECT:
So the correct query would be

Code: Select all

BETWEEN '2005-01-01' AND '2005-01-20 23:59:59'
or
BETWEEN '2005-01-01 00:00:00' AND '2005-01-20 23:59:59'

Using one date after is not correct as we would catch 2005-01-21 00:00:00 date also...which we do not want.

Posted: Tue Jan 03, 2006 6:21 am
by timvw
Hehe, that is one of the things that came up in my mind... But because you said explicitely said date in the first posting i thought that would be alright.. Anyway, case solved ;)