Re: Smalldatetime comparisons with non-clustered index



It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/microsoft.public.sqlserver.programming/msg/fe4f6ec635260e5a?dmode=source

I don't know if there is a knowledge base article about it, or a
proposed fix. The thread does show workarounds.

HTH,
Gert-Jan


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

/*
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)
.



Relevant Pages

  • Re: Weird Behaviour...argh
    ... wow, what a great bug;) ... there's a knowledge base article on this: ... thanks carl! ... > Martin Stich wrote: ...
    (microsoft.public.vc.language)
  • Re: Problem with checkboxes after Service Pack 3
    ... close to publishing a KB article and "fix" for this issue. ... Can you provide a link to a knowledge base article or a bug database ... where this bug is documented by Microsoft? ...
    (microsoft.public.access.forms)
  • onchange event fires incorrectly Q319741
    ... It appears that my .net controls are experiencing the bug defined in knowledge base article 319741. ... In a frame the onchange event of fires incorrectly. ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • Drop-down list in common dialog box "save as" closes itself
    ... I found this Knowledge Base article referring to a bug in Windows 2000: ... I got a problem that seems to be connected somehow: ...
    (microsoft.public.windowsxp.general)
  • Open/Save dialog
    ... This seems to be related to this known bug in IE: ... Microsoft Knowledge Base Article - 238588 - Internet Explorer Prompts the User with Two Open or Save Dialog Boxes ...
    (microsoft.public.dotnet.framework.aspnet)