Rouding VS CutOff Function

Question

Post reply

Rouding VS CutOff Function

Rofty

SSCrazy

Points: 2585
More actions
February 15, 2010 at 1:17 am

#138754

I have the following problem. I have a float value which can have a variable amount decimal placeholders. This float value needs to be cutoff on a certain amount of decimal placeholders.
Example:
[font="Courier New"]
43.39985323 -> 43.3998[/font]
But so far everything I've tried keeps giving me a rounded value of 43.3999. I have done casting and converting into String and Decimal Data Types, but seems like CONVERT and CAST is doing some rounding of their own.
[font="Courier New"]
PRINT CONVERT(VARCHAR(50),43.39985323) = '43.3999'
PRINT CONVERT(DECIMAL(8,4) ,43.39985323) = 43.3999
[/font]
I need some T-SQL functionality i can write into a UDF that will accept a Value and Digit Parameter through which i can cut of the float value at n specific number of decimal placeholders.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Rofty SSCrazy Points: 2585 More actions · Answer 1

This seems to do the job. Although process seems to be little over the top.

[font="Courier New"]

CREATE FUNCTION dbo.fnCutOff(@Value FLOAT, @Digit INT) RETURNS FLOAT AS BEGIN

DECLARE @IntValue BIGINT

DECLARE @CharValue NCHAR(50)

SET @IntValue = @Value * (POWER(10,@Digit+1))

SET @CharValue = CONVERT(NCHAR(50),@IntValue)

SET @IntValue = CONVERT(BIGINT,(SUBSTRING(@CharValue,1,LEN(@CharValue)-1)))

SET @Value = @IntValue

SET @Value = @Value / (POWER(10,@Digit))

RETURN @Value

END

[/font]

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

sqlusers SSCrazy Points: 2463 More actions · Answer 2

DECLARE @STR NVARCHAR(888)

SET @STR = '43.39985323'

SELECT SUBSTRING(@STR,1,CHARINDEX('.',@STR) + 4)

Rofty SSCrazy Points: 2585 More actions · Answer 3

The first problem there is you skipped a step - getting a FLOAT value to a STRING value.

Seeing as CONVERT AND CAST does it own freakish form of rounding the method you suggest will fail.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]