Posted by Robert Klemme on 04/12/06 19:11

Dimitri Furman wrote:
> 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

'2006-04-08 13:14:58.870' is not greater than '2006-04-08 13:15:00' - I
would not expect any results here.

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

Maybe casting makes it larger / rounds it? Note that smalldatetime has
minute as precision.

> 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.

Kind regards

robert

[Back to original message]