Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have encountered a problem where the WHERE clause is causing a problem. The varcharColumn is of datatype VARCHAR and contains numbers as well as text. The query looks to be averaging this column, but it has now hit a problem when a number has a decimal, e.g. 33.33.

SELECT AVG(CONVERT(INT,[varcharColumn])) 
  FROM View 
 WHERE [varcharColumn] > 0 
   AND [varcharColumn] <> 'NA' 
   AND CreatedDate > @StartDate AND CreatedDate <@EndDate

Now, I have tried to CAST and CONVERT the varcharColumn in the WHERE clause, but this returns no results. Any ideas?

share|improve this question
    
Do you want to round 33.33 or ignore it? –  gbn Jun 8 '12 at 9:58
2  
BTW: There is no guarantee that the WHERE [varcharColumn] <> 'NA' will run before the Cast to int. –  Martin Smith Jun 8 '12 at 10:01
    
@gbn: I would need to round it. –  Neil Knight Jun 8 '12 at 10:03

1 Answer 1

up vote 0 down vote accepted

Try this

    SELECT AVG(cast(cast([varcharColumn] as decimal) as int))
      FROM View 
     WHERE isnumeric([varcharColumn]) = 1 
     AND cast(cast([varcharColumn] as decimal) as int) > 0
     AND CreatedDate > @StartDate AND CreatedDate < @EndDate

enter image description here

share|improve this answer
    
CAST([varcharColumn] AS INT) > 0 will fail because it cannot convert '33.33' to INT. –  Neil Knight Jun 8 '12 at 10:05
    
this will select cast(CAST('33.33' as decimal)as int) –  AmmarR Jun 8 '12 at 10:12
    
Your edit worked :o) Thanks –  Neil Knight Jun 8 '12 at 10:20
4  
you are assuming a certain order of execution (the ISNUMERIC will be evaluated before the CAST) which is not guaranteed. –  Remus Rusanu Jun 8 '12 at 10:47
4  
This cannot be 'fixed', as is not a bug. Is an incorrect assumption on your side. The execution plan can change at any moment. Given that the cast and the check occur on the same column, is very unlikely to ever happen. But do not assume it can never happen. –  Remus Rusanu Jun 8 '12 at 11:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.