February 15, 2010 at 1:17 am
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]
February 15, 2010 at 1:55 am
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]
February 15, 2010 at 2:15 am
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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply