Why am I not getting a decimal result?

[Alibaba2003]

DECLARE @IncomingCurrency AS MONEY
SET @IncomingCurrency = 12345.6789

BEGIN
DECLARE @ToStringIncomingNumber as varchar
DECLARE @LenString AS INT
DECLARE @DPtPosition AS INT
DECLARE @X10000 AS INT
DECLARE @REMAINDER AS INT
DECLARE @FinalDbl AS DECIMAL
--DECLARE @HAORHO AS CHAR(2)
DECLARE @HOLDDECIMALVALUE AS DECIMAL
DECLARE @X10000EQUALIZER AS DECIMAL

if ( charindex('.', cast(@IncomingCurrency as varchar)) = 0)

SET @FinalDbl = @IncomingCurrency

else
Begin
set @DPtPosition = charindex('.', cast(@IncomingCurrency as varchar))
set @LenString = len(cast(@IncomingCurrency as varchar))
SET @X10000 = @IncomingCurrency * 10000
SET @REMAINDER = @X10000 % 100
If (@REMAINDER >= 50)
BEGIN
SET @HOLDDECIMALVALUE = (CAST(@X10000 AS DECIMAL) - CAST(@REMAINDER AS DECIMAL) + 100.00) / 100.00
--SET @HAORHO = 'HA'
END
ELSE
BEGIN
SET @HOLDDECIMALVALUE = (CAST(@X10000 AS DECIMAL) - CAST(@REMAINDER AS DECIMAL)) / 100.00
--SET @HAORHO = 'HO'
END

SET @FinalDbl = CAST(@HOLDDECIMALVALUE AS DECIMAL) / CAST(100 AS DECIMAL) * 1.0
end





SELECT @HOLDDECIMALVALUE AS 'THIS IS THE VALUE'



END

It's Nice to Be Important But It's more Important to BE Nice

 

[PLGREEN]

Declare all your decimal variables as DECIMAL(13,2). That way you will get decimal values.

Patrick Green

 

[JamesLean]

What is this procedure trying to do? There may be an easier, less complicated, way to do it.

--James

 

[Alibaba2003]

Thanks .. I admit i am new to sql server programming. What are those numbers stand for. Integer Scraps? what the difference; default?

It's Nice to Be Important But It's more Important to BE Nice

 

[Alibaba2003]

The procedure is trying to round the numbers to exactly 2 numbers after the decimal point. Round() and Money() will not do it because they return zeros after the first two numbers.

Thanks

It's Nice to Be Important But It's more Important to BE Nice

 

[JamesLean]

;-)

DECLARE @IncomingCurrency AS MONEY
SET @IncomingCurrency =  12345.6789

SELECT CAST(@IncomingCurrency AS decimal(10, 2))

--James

 

[SQLBill]

With DECIMAL, you include two values - precision and scale. One is the full length of the DECIMAL value and the other is the number of digits to the right of the decimal point.

ie. DECIMAL(10,2) is:

12345678.90

but not

1234567.890

-SQLBill

Posting advice: FAQ481-4875

 

[Alibaba2003]

DOOOOH! THANKS JAMESLEAN.

It's Nice to Be Important But It's more Important to BE Nice