Try this

select * from test where convert(char, chkdate, 112)  = '20040106'

But this will not use any index. 

If you want the query to use an available index on chkdate...

Thank you AdiCohn. It is very helpful.

SELECT *

FROM Clinic

WHERE Date BETWEEN '20031209' and '20031210'

ADICOHN, r u sure that this will always return only the data for 09 Dec 2003 ???

If it is...

Try this

select * from Clinic where convert(char, Date, 112) = '20030912'

Mr.Bkelly,

Please correct me if i am wrong on assuming the following, based on your queries:

1) Having a single user to log-in to SQL server will not have any adverse...

AdiCohn, I am not disagreeing with you. The problem may be that I have not used the options in SQL Server. When I am developing the next system I...

Thank you AdiCohn for your comments.

I am experiencing two problems in changing the application-user to Sql-user.

1) My system is too complex having almost 350 tables and 80 or more data...