Page 1 of 1

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

Posted: Sun May 14, 2006 12:28 pm
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!

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

Posted: Sun May 14, 2006 12:39 pm
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...

Posted: Sun May 14, 2006 12:51 pm
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...

Posted: Sun May 14, 2006 1:35 pm
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

Posted: Sun May 14, 2006 2:55 pm
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'

Posted: Sun May 14, 2006 4:33 pm
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.