3

I am attempting to do a calculation in SQL Server 2008 R2 but I keep getting an error of

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value '.05' to data type int.

Altering table structure is not an option, is there a way I can alter the query so that this will succesfully execute?

DECLARE @Test1 TABLE
(
  field1 VARCHAR(10),
  field2 INT
);

INSERT INTO @Test1 ( field1, field2 )
    VALUES ( '.05', 12 );

SELECT field1 / field2 
FROM @Test1;
  • 4
    Why is altering the table structure not an option? Whose decision was it to store numbers in a string column? What is preventing non-numbers from being introduced into that column? – Aaron Bertrand Feb 17 '17 at 0:38
  • 1
    @AaronBertrand Table variables are forever. – Erik Darling Feb 17 '17 at 0:52
  • 3
    @sp_BlitzErik Well, I assume that's just the MVCE and that in real life Yohan has a real table. – Aaron Bertrand Feb 17 '17 at 1:01
  • 1
    @AaronBertrand Probably. I see the varchar field for decimals pretty often when weird numbers that end up as scientific notation on insert start happening. – Erik Darling Feb 17 '17 at 1:03
  • 1
    These are the reasons we'll have work for a long time. – Max Vernon Feb 17 '17 at 4:33
6

Is this what you want to happen?

DECLARE @Test1 TABLE
(
  field1 VARCHAR(10),
  field2 INT
);

INSERT INTO @Test1 ( field1, field2 )
    VALUES ( '.05', 12 );

SELECT field1 / CONVERT(DECIMAL(18, 2), field2)
    FROM @Test1;

Since this was marked as the answer: I converted the integer value to a decimal because of data type precedence. The string '.05' can't be converted to an integer, but it makes a perfectly fine decimal.

  • You may want to add some details about why you chose 18,2. I know I'd like to know. – Max Vernon Feb 17 '17 at 4:34
  • 3
    @MaxVernon only because the one example decimal was two places. The project specs were pretty slim ;) – Erik Darling Feb 17 '17 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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