Rounding Off issue in SQL Server

• vikramchander90

  Ten Centuries

  Points: 1095

  More actions

  September 18, 2025 at 6:14 am

  #4653807

  I am using Microsoft SQL Server 2022 (RTM-CU17) (KB5048038) - 16.0.4175.1 (X64)  Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022

  Facing issue in rounding off where this query is retuning 0.0000170000 instead of 0.00001672. Actual datatype is numeric(28,8), tried increasing precision - changing data type (which i cannot do in production) to Decimal but for testing purpose tried this, using cast function but its not returning the expected value of 0.00001672

  declare @requested_qty   numeric(30,10) = 0.00001672,

  @conv_fact_nr    numeric(30,10) =1,

  @conv_fact_dr    numeric(30,10) = 1

  Select @requested_qty = @requested_qty * (@conv_fact_nr /@conv_fact_dr)

  Select @requested_qty '@requested_qty'

  If SET NUMERIC_ROUNDABORT ON; is Set, then facing error - arithmetic overflow converting data type to numeric error is occurring.

  Can you please suggest.

   

  The variable @conv_fact_nr  is not always 1 and is derived based on conversion factor