Query Manager SQL - Decimal Precision

[Former Member]

I'm having a real hard time with a formatted search and I figured out that it has to do with the decimal precision. Two values that are multiplied together, if one is a decimal and the other is a number >= 1.0, it works. If both values are < 1.0, it has problems. If I add the two numbers together that are < 1.0, the result is fine. If I multiple two numbers together that are < 1.0 it doesn't. The query below demonstrates this.

declare @OrderTotal float

declare @OrderExp float

declare @DiscPct float

set @OrderTotal = .75

set @DiscPct = 2

set @DiscPct = @DiscPct / 100

set @OrderTotal = @OrderTotal * @DiscPct

select @OrderTotal

Result is 1.5 instead of .15 (.02 x .75)

If one changes the .75 to 1.0, the result is correct a .02

I played around with Float and Real, but I don't know enough about either. Any help would be greatly appreciated.

[Former Member]

Perfect. Thank you very much

[Former Member]

Hi Scott,

I believe you are finding the Forums helpful.

Just a quick note. Whenever a members reply answers your question, you may award points to that member based on the usefullness as a token of reciprocation.

This really helps keep the morale high.

Petr is one of our very knowledgeable members and I believe deserves points for his answer

Best wishes

Suda

[Former Member]

Thanks Suda

[Former Member]

.02 x .75 is 0.015 and not 0.15 as you wrote. I run this query and the result is correct, so I think that it belongs to your sql settings.

Try to change it as

declare @OrderTotal numeric(9,6)

declare @OrderExp numeric(9,6)

declare @DiscPct numeric(9,6)

set @OrderTotal = .75

set @DiscPct = 2

set @DiscPct = @DiscPct / 100

set @OrderTotal = convert(numeric(9,6), @OrderTotal * @DiscPct)

select @OrderTotal

and let me know, if it works