"Kapoor, Nishikant" <[EMAIL PROTECTED]> wrote on
08/01/2005 09:55:21 AM:
> I am probably missing something very simple, but appreciate it if
> someone could point me to that.
>
> I am doing this query on following table to fetch recs for a month:
>
> SELECT fName, lName, acctOpenDate FROM test WHERE acctOpenDate >=
> '2005-07-01' AND acctOpenDate <= '2005-07-31';
>
> I expect to see all 4 rows, but I see only the latter two. The ones
> dated '2005-07-31' are not listed even though I am using
> acctOpenDate <= '2005-07-31'.
>
> fName lName acctOpenDate
> Ccc Cc 2005-07-30 21:08:51
> Ddd Dd 2005-07-30 05:05:48
>
> I am using "mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu
(i586)"
>
> DROP TABLE IF EXISTS `test`;
> CREATE TABLE `test` (
> `fName` varchar(10) NOT NULL default '',
> `lName` varchar(10) NOT NULL default '',
> `acctOpenDate` datetime NOT NULL default '-00-00 00:00:00'
> ) TYPE=MyISAM;
>
> # Dumping data for table `test`
>
> INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Aaa',
> 'Aa', '2005-07-31 20:56:06');
> INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Bbb',
> 'Bb', '2005-07-31 10:51:06');
> INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Ccc',
> 'Cc', '2005-07-30 21:08:51');
> INSERT INTO `test` (`fName`, `lName`, `acctOpenDate`) VALUES ('Ddd',
> 'Dd', '2005-07-30 05:05:48');
>
> Thanks much,
> Nishi
>
Yes, it is something simple that you are missing. Don't feel bad, this
same simple thing has thrown me for a loop a time or two.
It's in your comparison with your end date. What you are comparing it is
<= 'some date value'. When you do that, the value of 'some date value' is
expanded to include the time value of '00:00:00' so that what you are
doing is comparing your data to the extreme earliest START of the date.
Anything logged within that date (say at 9AM) will not be found with this
comparison.
Two options:
a) explicity state the latest possible time for your ending date:
AND acctOpenDate <= '2005-07-31 23:59:59'
b) look for values LESS than the start of the NEXT day:
AND acctOpenDate < '2005-08-01'
make sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
