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!
Greater than or equal to, does not seem to be working
Moderator: General Moderators
Re: Greater than or equal to, does not seem to be working
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...
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:
ex:
Code: Select all
SELECT * FROM `someTable` WHERE date(myDate) <= '2006-05-13'- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
You might also want to look at the BETWEEN MySQL function...
FYI, BETWEEN is an inclusive search on both ends.
Code: Select all
$sql = "SELECT field FROM table WHERE field BETWEEN lowerval AND upperval";