I want to query a date filed in SQL Server 2000 which its default value is set to GetDate().
My problem is that the data filed saves data and time for example "3/26/2003 5:34:34 PM", Now when I query the table and:

SELECT * FROM tblX WHERE Date='3/26/03' it won't return the record.

However if I use the below:

SELECT * FROM tblX WHERE Date='3/26/2003 5:34:34 PM'

It will return the record.

What should I do so that when I use only date and not time it retuns all records on the specified Data ignoring the time?

Thanks

essentialy you want all the records for Mar 26th?

1. If you don't need time only store the date portion of GetDate(). Probably not an option.

select convert(varchar,getdate(),101)

2. Remove the time component in your date attribute in your search. This approach will probably cause a table scan.

WHERE convert(varchar,date,101)='3/26/2003'

3. Use the between test. Some times can be extra work.

WHERE date between '3/26/2003' and '3/26/2003 23:59:59'