problem with BETWEEN AND with dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

problem with BETWEEN AND with dates

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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))
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ;)
Post Reply