Index Creation Causes Error The Conversion Of A Char Data Type To A Datetime Data Type Resulted...

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!