Transact SQL – Error when creating an index on a View

Error Message

Here is the error message:

Cannot create index on view ‘v_myIndexView’ because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

Code

View – Original


create view dbo.v_sales
with schema_binding
as
     select companyID, buyDate
     from  dbo.sales
     where buyDate != '01/01/1990' 

To Correct try:

View – Revised


-- Explicit conversion specifying style value
-- 101 USA mm/dd/yy 
create view dbo.v_sales
with schema_binding
as
    select companyID, buyDate
    from  dbo.sales
    where buyDate != convert(datetime,'01/01/1980',101)

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s