[Bug?] Where clause in SQLSERVER7 doesn't return results where it really should

Hi group,
I found the following behaviour really surprising:
[SQLServer 7]
consider following query:
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08') AND (logdate <= '2006-02-08'))
ORDER BY logdate;
--
This query returns 0 results.
If I remove one part of the where-clause, it works just fine:
Like this
--
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate <= '2006-02-08'))
ORDER BY logdate;
--
or
SELECT L.logid, L.logdate, L.vragensetid, L.dooruserid, L.incidentid,
L.action, U.realname
FROM tbllog AS L LEFT OUTER JOIN tbluser AS U ON (L.dooruserid=U.userid)
WHERE((logdate >= '2006-02-08'))
ORDER BY logdate;
--
(These queries only differ in the >= or <= in the whereclause.)
Both queries return some rows!
(As expected by me because I know the table does have rows with logdate
'2006-02-08')
It seems that SQLServer is unable to find ANY records if I use both
whereclauses at the same time.
The reason this weird situation is needed is because I need to assemble the
query dynamically based on some form-info posted by a user.
Can anybody explain this?
Is this a known bug?
Or is maybe something going on that has to do with my (limmited)
understanding of SQLServer/SQL '
I can of course code around this issue by checking in my script if the dates
are the same and in that case make only whereclause (logdate='2006-02-08'),
but I am curious what is going on.
Thanks for your time!
Regards,
Erwin MollerErwin Moller wrote:
Hi, I made the situation even simpler, and found the reason for my problem.
I removed the join, and left only 1 where-clause.
So the following query:
SELECT logid, logdate, vragensetid, dooruserid, incidentid, action
FROM tbllog
WHERE(logdate <= '2006-02-08') ORDER BY logdate;
--
Resulted in NO results.
Where the database contains records on this date.
Reason is: I am a simpleminded idiot.
I do not tell the database how many hours/minutes/seconds....
So while there are records on 2006-02-08, they are ALL after midnight.
:-)
Sorry for the noise!
Regards,
Erwin Moller