Cannot search for a date in a DateTime column having TimeStamp

[Bik]

I have a DateTime datatype column, with records showing as:

2002-10-05 10:29:29.001
2002-10-05 10:29:30.245
2002-10-10 10:29:31.324
2002-10-07 10:29:32.454
2002-10-07 10:29:33.343
2002-10-07 10:29:34.565
2002-10-05 10:29:35.353
2002-10-05 10:29:36.578
2002-10-05 10:29:37.943
2002-10-01 10:29:38.115
2002-10-01 10:29:39.564

and I want to search for records with date, say 2002-10-07, and it not work if I search with '10/07/2002' but if I give, say '10/07/200 10:29:34.565' it will only pull up that one record. I want to pull all 3 records having date of 10/07/2002.

EXAMPLE 1:
SELECT email_address AS Email, Sent
FROM email_address
WHERE Send = '#10/07/2002#'

shows (0 row(s) affected)

[Stephen]

Unless you specify the time portion of the date, SQL Server defaults it to midnight (00:00:00) "this morning". Hence your query is being interpreted as give me all records with a date of midnight on the 7th of October.

What you will need to do is to select records between midnight and 23:59:59 on the 7th of October.

And a word of warning - be careful with the format of your date. SQL will interpret it based on the regional settings. If at all possible, using unambiguous dates e.g. '7 Oct 2002' instead of 07102002 or 10072002 or 20021007. I have seen an application working quite happily for many months suddenly fail because one of the sys admins decided that a change in the regional settings would be a good thing !

[Bik]

I was able to find the solution, based on one of the postings by CS_TROYK on 9/27/02.

SELECT email_address_ID as ID,
email_address AS Email,
Sent
FROM email_address
WHERE CAST(CAST(CAST(Sent AS real)As int)As datetime) = '10/10/2002'

It works great.

Thanks Stephen for posting your reply to my message.
Thanks to CS_TROYK