BETWEEN giving me grief

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
aquanutz
Forum Commoner
Posts: 28
Joined: Thu Sep 14, 2006 9:07 am

BETWEEN giving me grief

Post by aquanutz »

Alright, I have a date from my calendar that I am trying to see if it is within a rang of dates in a table in MySQL. I am using BETWEEN (as per my thread yesterday) but it is bringing back every entry in the table and not just the ones that match the BETWEEN statement.

here is what I have (Using dates instead of variables to make it easier):

select * from intersessionDates where DATE '2006-12-16' between DATE '2006-12-15' and DATE '2007-01-07';

and it brings back:
+-----+------------+------------+
| num | startDate | endDate |
+-----+------------+------------+
| 1 | 2006-08-04 | 2006-08-20 |
| 2 | 2006-12-15 | 2007-01-07 |
+-----+------------+------------+

It should only bring back the Second tuple...

I've tried it with and without the 'DATE' on there and I've tried putting times on the end too, nothing works... Any thoughts?

thanks.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

a) without DATE
b) there's no field in that where clause, only static dates as string literals. What do you want to compare? (rhetorical question)
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

When storing date columns in any database always use either the built in date type or an int that holds either an ISO8601 style date (YYYYMMDD) or a timestamp.

The reason your having issues is because MySQL is treating those dates as strings not interger values or even date values. So you will need to convert to either mysql's date type or use an int and store your dates in iso8601 or as timestamps. I would use iso8601 as its easily readable, easily portable and you can even use it with mysql's date functions even though its an int and not a date type.

hope that helps.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Code: Select all

SELECT * FROM table_name WHERE '2006-12-16' BETWEEN startDate AND endDate

EDIT: forgot single quotes. fixed.
aquanutz
Forum Commoner
Posts: 28
Joined: Thu Sep 14, 2006 9:07 am

Post by aquanutz »

GM, once again you come through. THANKS!

Ody, Volka, thanks for your replies! Volka, I realized that you meant what GM said just as he replied. Ody, I konw what you mean and from now on I will make it like that.

Thanks guys.
Post Reply