Error when summing converted column

Question

I have a table in SQL Server that contains 2 columns: Question_asked, Response

The Response column is of the datatype varchar(500) and holds the users response to the question that is held in the column Question_asked

I have created a view V_age_attr with the following SQL statement:

select 
    question_asked, cast(Response as integer) 
from 
    main_table 
where 
    question_asked = 'What is your age'

If I run a simple SQL query the results return as expected. But in some circumstances like when using a sum/group by query. I get an error msg (actual msg I get):

Conversion failed when converting the varchar value '2011-08-13 00:00:00' to data type int

In the table, main_table, one of the questions is the start_date, so the column Response does hold date values in the table, but not in the view I created.

Again, I can run a select query and check all the values for my cast(Response as integer) and they all are integer values.

My main question is: can anyone tell me if this is known/expected behavior in SQL Server 2008 R2, and/or does anyone else have another way to get a subset of values from this type of table other than creating a view?

Thank you

It would be helpful if you could show a query where you have the problem. — Guffa, Sep 29 '12 at 17:18

score 3 · Answer 1 · answered Sep 29 '12 at 17:39

Alas, filtering by isnumeric doesn't necessarily work. SQL is a descriptive language, not a procedural language. So, the operations do not necessary work in the order specified.

In this case, SQL Server will attempt to do the conversion when reading the data and then apply the filter. Too late. You have an error.

The case statement is the only statement that guarantees order of evaluation, at least when aggregation functions are not involved. The following should fix the conversion problem:

select question_asked,
       (case when isnumeric(Response) = 1 and
                  Response not like '%.%'
             then cast(Response as int)
        end) as Response
from main_table
where question_asked = 'What is your age'

This version also checks for a decimal point in Response. ISNUMERIC will return 1 when there is a decimal point, but that will fail the conversion as well.

score 1 · Answer 2 · answered Sep 29 '12 at 21:22

Given the bug that Martin has highlighted, I would actually use the original condition in the CASE statement, which will apply to any numeric type, not limited to Gordon's int interpretation. Better than ISNUMERIC, but TRY_CONVERT would be better from SQL Server 2012 onwards. You would hope SQL Server can re-use the condition resolution in the WHERE clause to assist in the SELECT, or vice-versa.

select 
    question_asked, case when question_asked = 'What is your age'
                         then cast(Response as integer)
                         end
from 
    main_table 
where 
    question_asked = 'What is your age'

score 0 · Answer 3 · edited May 23 '17 at 10:24

0

Filter using ISNUMERIC - there may be non-integral data as responses which are messing up the aggregate functions.

SELECT
 question_asked,
 CASE(response AS INT)
FROM
 main_table
WHERE
 question_asked = 'What is your age'
 AND ISNUMERIC(response) = 1

Edit: if that still doesn't work, you may need to do some more aggressive type-checking on your response column. Check out the responses here:

How to get if a string is a number in T-SQL

edited May 23 '17 at 10:24

Community

1
1

answered Sep 29 '12 at 17:16

eouw0o83hf

9,438
5
53
75

2

You are assuming the `WHERE` will happen before the cast as int. [This isn't guaranteed](http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors) – Martin Smith Sep 29 '12 at 17:20
Wow - how about that... I've never actually run into that problem in the wild before. I guess the only foolproof method would be a subselect/CTE that pulls out rows with numeric `response`s, then feeds a final select statement. – eouw0o83hf Sep 29 '12 at 17:24
1

The workaround is to use `CAST(CASE WHEN ISNUMERIC(response) = 1 THEN response END AS INT)`. Or if SQL Server 2012 use `TRY_CONVERT(int, response)` – Martin Smith Sep 29 '12 at 17:38
@Martin I added an answer. I prefer to stay far away from ISNUMERIC – RichardTheKiwi Sep 29 '12 at 21:22

Error when summing converted column

3 Answers3