[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 Moller
Erwin 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

[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

@@servername gives old name

Hi
I have changed mycomputername from test1 to test2
I can connect to sql server 2000 in query analyzer using test2 name , but
when I give query
select @.@.servername is gives old name test1
why this happens?
ThanksChanging the servername is not only a thing on windows, there are entries
you have to make via stored procedure on the systables, what does the table
sysserver tell (located in master)
HTH, Jens Suessmeyer.
"AM" <anonymous@.examnotes.net> schrieb im Newsbeitrag
news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>|||Changing the name of your server does not automatically change the name of t
he
SQL Server instance. Changing the name of a server running SQL Server is a
significant undertaking and should not be done without considerable forethou
ght.
Here's a link on the Microsoft KB that might help:
http://support.microsoft.com/defaul...kb;en-us;303774
Of course, this doesn't account for all applications that will have to chang
e to
point to the new server.
Thomas
"AM" <anonymous@.examnotes.net> wrote in message
news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>|||Execute the following on your server:
sp_dropserver <old_name>
go
sp_addserver <new_name>
go
When you rename a server, it doesn't update the master..sysservers table,
which is where @.@.servername gets its value. In SQL Server Books Online go
to index and type "Renaming servers" and you'll get a good topic discussing
it.
Mike
"AM" <anonymous@.examnotes.net> wrote in message
news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>|||http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AM" <anonymous@.examnotes.net> wrote in message news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.ph
x.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>