question

MS_slave_007 avatar image
MS_slave_007 asked

in sql: how do i convert a string to an int and filter on the result. I get Conversion failed

i have this query already which returns the result below. How do i retrieve only the records after say 2005 ? (I use MS SQL Server 2005)

select AdId,DetailName,buildyear from (                    
select result.AdID,result.DetailName,cast(result.DetailValue as integer) as buildyear from (                    
                    
    SELECT     AdDetails.AdID,AdDetails.DetailName,AdDetails.DetailValue                    
    FROM                             
    database1.dbo.AdDetails as AdDetails,                    
    database1.dbo.detailType as detailType                    
    WHERE                     
    AdDetails.DetailTypeID = detailType.DetailTypeID                    
    and detailType.DetailName = 'BuildYear'                     
                    
) as result                    

result:

AdID, DetailName, buildyear                    
34609809 BuildYear 2002                    
38479809 BuildYear 2009                    

when i change it too this i get the error message below

select AdId,DetailName,buildyear from (                    
select result.AdID,result.DetailName,cast(result.DetailValue as integer) as buildyear from (                    
                    
    SELECT     AdDetails.AdID,AdDetails.DetailName,AdDetails.DetailValue                    
    FROM                             
    database1.dbo.AdDetails as AdDetails,                    
    database1.dbo.detailType as detailType                    
    WHERE                     
    AdDetails.DetailTypeID = detailType.DetailTypeID                    
    and detailType.DetailName = 'BuildYear'                     
                    
) as result                    
) as foobar                    
where foobar.buildyear> 2008                    

the error message i get: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'Red' to data type int.

I know what the error means ofcourse, but why it complains i did convert it as you can see.... Might this be a bug , or what am i doing wrong here ?

sql-server-2005
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image Squirrel 1 commented ·
check the value for AdDetails.DetailValue, one of the row contain the value 'Red' you can use isnumeric() to do that
0 Likes 0 ·

1 Answer

·
RickD avatar image
RickD answered

As Squirrel says, use ISNUMERIC() or fix your data.

Change

where foobar.buildyear> 2008            

to

where isnumeric(foobar.buildyear) = 1 and foobar.buildyear> 2008            

If you can not fix your data.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.