473,224 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Smalldatetime comparisons with non-clustered index

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)
Apr 12 '06 #1
5 2659
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

Apr 12 '06 #2
It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/micros...a?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)

Apr 12 '06 #3
Gert-Jan Strik (so***@toomuchspamalready.nl) writes:
It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/micros...amming/msg/fe4
f6ec635260e5a?dmode=source
I don't know if there is a knowledge base article about it, or a
proposed fix. The thread does show workarounds.

The bug is also in SQL 2005 RTM. (I don't have the CTP of SP1 running
right now.) Do you if has been reported on
http://lab.msdn.microsoft.com/ProductFeedback/ for SQL 2005?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 12 '06 #4
On Apr 12 2006, 06:27 pm, Erland Sommarskog <es****@sommarskog.se> wrote
in news:Xn*********************@127.0.0.1:
Gert-Jan Strik (so***@toomuchspamalready.nl) writes:
It is a bug. For an example of an earlier discussion, see

http://groups.google.nl/group/micros...programming/ms
g/fe4 f6ec635260e5a?dmode=source

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

The bug is also in SQL 2005 RTM. (I don't have the CTP of SP1 running
right now.) Do you if has been reported on
http://lab.msdn.microsoft.com/ProductFeedback/ for SQL 2005?


I haven't found anything, so I went ahead and opened a bug:
http://lab.msdn.microsoft.com/Produc...px?feedbackId=
FDBK48623

Feel free to add anything that may be important.

--
(remove a 9 to reply by email)
Apr 13 '06 #5
Dimitri Furman (df*****@cloud99.net) writes:
I haven't found anything, so I went ahead and opened a bug:
http://lab.msdn.microsoft.com/Produc...px?feedbackId=
FDBK48623

Feel free to add anything that may be important.


Good!

I tested it on the CTP of SP1, and the bug appears there as well.

I final blow to anyone who is in doubt over whether this is a bug is
that if I add WITH (INDEX = 1), that is index hint force the clustered
index to be used, the query returns a non-empty result set.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 13 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Joey Martin | last post by:
How do I convert an nvarchar field to smalldatetime format? My nvarcharfield (saledatetext) is in the format YYYY-MM-DD. I want to convert is to smalldatetime (field: saledate) Thanks for...
2
by: Joey Martin | last post by:
Please help. I have a ms sql table called data. Two fields, saledate(smalldatetime),saledatetext(nvarchar) I have dates (ex. 05/04/05) in the saledatetext, but I need this converted into...
7
by: Marc Pelletier | last post by:
Hello, I have a table with a Day field, defined as smalldatetime. I am filling it from a CSharp application with the following code: DataRow r = dtStaDays.NewRow(); r= station_ID; r =...
7
by: Bostonasian | last post by:
I have a table that contains transactional data. Such as site view by whom, when, which template, etc, etc... Everytime when I pulled the report, hh:mm:ss never matters. Only breakdown by dates,...
2
by: Marc Pelletier | last post by:
Hello all, I have a library of datetime routines that make things simpler for me. Before I discovered DateTime.MinValue I had a function that looked like public static DateTime DayZero { get {...
4
by: Aamir Mahmood | last post by:
Hi I am on framework version 1.1 (SP1). Consider the following code. public static void Main() { int i=1; int j=1;
2
by: Salim Afar | last post by:
Hi, How can I cast from datetime type to smalldatetime type. I get the records from a table which has got a datetime type column but I want to display it smalldatetime format. Thanks, Salim
2
by: .Net Sports | last post by:
I'm using these to assign a variable to a smalldatetime object in sql server: dim todnews = DateTime.Today.ToString ( "d" ) 'connection string to server is on this line Dim strSQL2 as string...
6
by: SQL Server | last post by:
I've been working this for a while. Kind of new to SQL Server functions and not seeing what I am doing wrong. I have this function CREATE FUNCTION dbo.test (@Group varchar(50)) RETURNS...
2
by: sallyme | last post by:
Hi I am passing Date value form .asp form like "3/15/2007" And i am geting error Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) Syntax error converting character string to...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.