none
Inconsistent Arithmetic overflow error.

    Question

  • SELECT SUBSTRING('TEST12', 5, 2) * 13.0
    SELECT SUBSTRING('TEST12', 5, 2) * 9.0

    When I execute the above 2 selects in SQL Server 2012 enterprise edition, first query returns result but the second query returns Arithmetic overflow error. Why the results are inconsistent? 

    Note: Unable to attach the image as my account was not yet verified.


    Wednesday, June 13, 2018 8:36 PM

Answers

  • 13.0 is implictly converted as Numeric (3,1) where as 9.0 as Numeric (2,1).

    So 2nd statement is failing as the result expected out of second multiplication is 108.00

    You can check this by running this ,

     SELECT SUBSTRING('TEST9', 5, 2)* 9.0

    Run below queries to see the column type 

    select 13.0 as t into test
    sp_help test
    go
    select 9.0 as t into test1
    sp_help test1
     

    OR explicit casting 9.0 

    SELECT SUBSTRING('TEST12', 5, 2)* cast(9.0 as numeric(3,1))


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    • Edited by Sarat (SS) Wednesday, June 13, 2018 8:48 PM
    • Proposed as answer by Mohammad Waheed Wednesday, June 13, 2018 9:20 PM
    • Marked as answer by Karthikeyan81 Wednesday, June 13, 2018 9:37 PM
    Wednesday, June 13, 2018 8:46 PM

All replies

  • 13.0 is implictly converted as Numeric (3,1) where as 9.0 as Numeric (2,1).

    So 2nd statement is failing as the result expected out of second multiplication is 108.00

    You can check this by running this ,

     SELECT SUBSTRING('TEST9', 5, 2)* 9.0

    Run below queries to see the column type 

    select 13.0 as t into test
    sp_help test
    go
    select 9.0 as t into test1
    sp_help test1
     

    OR explicit casting 9.0 

    SELECT SUBSTRING('TEST12', 5, 2)* cast(9.0 as numeric(3,1))


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    • Edited by Sarat (SS) Wednesday, June 13, 2018 8:48 PM
    • Proposed as answer by Mohammad Waheed Wednesday, June 13, 2018 9:20 PM
    • Marked as answer by Karthikeyan81 Wednesday, June 13, 2018 9:37 PM
    Wednesday, June 13, 2018 8:46 PM
  • Thank you so much.
    Wednesday, June 13, 2018 9:37 PM