Greater than or equal to, does not seem to be working

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
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Greater than or equal to, does not seem to be working

Post by GeXus »

I'm selecting between two dates. And I have the following DT >= DateStart and DT <= DateEnd

The problem is that the DateEnd does not seem to be using the "equal to"... because it only returns results BEFORE that time..

For example if I pick 20060501 to 20060506 it will not return any values where the date is May 6th.....

Do I have to add one day to the end date? or is there something I am doing wrong?


Thanks!
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: Greater than or equal to, does not seem to be working

Post by GeXus »

GeXus wrote:I'm selecting between two dates. And I have the following DT >= DateStart and DT <= DateEnd

The problem is that the DateEnd does not seem to be using the "equal to"... because it only returns results BEFORE that time..

For example if I pick 20060501 to 20060506 it will not return any values where the date is May 6th.....

Do I have to add one day to the end date? or is there something I am doing wrong?


Thanks!


Actually I think i found the reason.. It's because the DT is actually datetime, so the time is past the actual day.... although I am using date_format, but I guess that doesnt truncate any data, it simply formats it or excludes the time...
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

that's why I always use DATE or DATETIME fields in my db. I've never been convinced for the reasons to use unix timestamps...
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Burrito wrote:that's why I always use DATE or DATETIME fields in my db. I've never been convinced for the reasons to use unix timestamps...
Yeah, I'm using DATETIME
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

in that case just use the date() function around your field name and it will parse it as a date and your >= or <= will work perfectly...

ex:

Code: Select all

SELECT * FROM `someTable` WHERE date(myDate) <= '2006-05-13'
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You might also want to look at the BETWEEN MySQL function...

Code: Select all

$sql = "SELECT field FROM table WHERE field BETWEEN lowerval AND upperval";
FYI, BETWEEN is an inclusive search on both ends.
Post Reply