Simple Select with date clause [Resolved]

[ober0330]

I am running MSSQL 2K SP3 and using a very simple select statement to grab some files from a table:

SELECT * FROM [Hours] WHERE InputDate = '11/16/2004' AND CType = 1 AND ReadNum = 2 ORDER BY CellID

The datatype for the field is smalldatetime and I've tried several combinations of dates and times, and even used the LIKE operator. The only way I can get it to work is to use the BETWEEN operator and check for the day before and the day after.

Why isn't this working??? And yes, there are records with that date and the records meet the other clause needs. If I take the date portion out, it works but grabs more data than I want.

[vb_dba]

Check the actual values of the InputDate field. Chances are they are also storing the time. If that is the case, then you are effectively comparing '11/18/2004 09:07:33' to '11/18/2004 00:00:00', which doesn't match. One option is to change the comparison to:

SELECT * 
FROM [Hours]
WHERE Convert(VarChar(10), InputDate, 101) = '11/16/2004' 
AND CType = 1 
AND ReadNum = 2 
ORDER BY CellID

That would cause the date to be converted to the mm/dd/yyyy format that you could use to compare against your parameter.

[ober0330]

That's why I love this board. Thanks!