This looks like a bug - hopefully somebody can explain what is actually
happening. Using SQL Server 2000 SP4.

Here's a repro script with comments:

/* repro table */
CREATE TABLE dbo.T (
ID int NOT NULL,
Time datetime NOT NULL,
CONSTRAINT PK_T PRIMARY KEY (ID, Time)
)
GO

/* the problem does not happen without this index */
CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
GO

/*
sample row - note that
CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
*/
INSERT INTO dbo.T (ID, Time)
VALUES (1, '2006-04-08 13:14:58.870')
GO

/*
This does not return any rows - why?
The comparison should evaluate to TRUE.
*/
SELECT *
FROM dbo.T
WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
GO

/*
This does return the row.
*/
SELECT *
FROM dbo.T
WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
'2006-04-08 13:15:00'
GO

DROP TABLE dbo.T
GO

The difference between the two SELECT statements is that the first one uses
a non-clustered index seek, whereas the second one uses a scan of the same
index.

--
(remove a 9 to reply by email)