Index creation causes error "The conversion of a char data type to a datetime data type resulted..."

[Amir]

Hi all,

I have a table called PTRANS with few columns (see create script below).

I have created a view on top that this table VwTransaction (See below)

I can now run this query without a problem:

select * from dbo.VwTransaction
where
AssetNumber = '101001' and
TransactionDate <= '7/1/2003'

But when I create an index on the PTRANS table using the command below:

CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)

The same query that ran fine before, fails with the error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

I can run the same query by commeting out the AssetNumber clause and it
works fine. I can also run the query commenting out the TransactionDate
column and it works fine. But when I have both the conditions in the
WHERE clause, it gives me this error. Dropping the index solves the
problem.

Can anyone tell me why an index would cause a query to fail?

Thanks a lot in advance,
Amir



CREATE TABLE [PTRANS] (
[CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHCENT] [numeric](2, 0) NOT NULL ,
[CHYYMM] [numeric](4, 0) NOT NULL ,
[CHDAY] [numeric](2, 0) NOT NULL ,
[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]
GO

CREATE VIEW dbo.vwTransactions
AS
SELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -
LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -
LEN(CHYYMM))
+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -
LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,
CHL# AS AssetNumber,
CHTC AS TransactionCode
FROM dbo.PTRANS
WHERE (CHCENT <> 0) AND (CHTC <> 'RA')

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Amir (no****@devdex.com) writes:

I have a table called PTRANS with few columns (see create script below).

I have created a view on top that this table VwTransaction (See below)

I can now run this query without a problem:

select * from dbo.VwTransaction
where
AssetNumber = '101001' and
TransactionDate <= '7/1/2003'

But when I create an index on the PTRANS table using the command below:

CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)

The same query that ran fine before, fails with the error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
...
Can anyone tell me why an index would cause a query to fail?

Apparently the table includes data from which a date cannot be composed
(or which your view does not handle properly.)

In this case it's up to chance whether this value would cause trouble
like the one above. If SQL Server first filters on AssetNumber, you
are fine, if the bad date is for another asset number. But if SQL Server
first looks at the date, you lose.

As for your view, I'm a little worried about the add_dashes thing.
Stick to the format YYYYMMDD HH:MM:SS, since this format is always
understood by SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp