Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ltrim rtrim Causing Floating Field to Round up?? Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 1:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
Hello -

I have a Select query that pulls data from a view. In my Select query I have the line
ltrim(rtrim([Revenue])) AS 'Revenue'

I'm finding out that if I remove the ltrim(rtrim the revenue number comes up correct as 10041.25 but with the ltrim(rtrim back in it gets rounded up to 10041.30.

I didn't think this would do this. Is there a way to fix this?

Regards,
David
Post #1507329
Posted Tuesday, October 22, 2013 1:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 3,582, Visits: 8,041
LTRIM and RTRIM won't chage values of a character string. The problem might be an implicit conversion if your Revenue column is not a character type. To avoid this problem, you should do an explicit conversion.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1507330
Posted Tuesday, October 22, 2013 1:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 9:58 AM
Points: 92, Visits: 679
Thank you for the fast response. You stated
To avoid this problem, you should do an explicit conversion.


My column is
Revenue (float, null)

How would I do the explicit conversion? On the view or select statment?

Regards,
Post #1507340
Posted Tuesday, October 22, 2013 2:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Why are you trimming a float in the first place?
A float cannot have leading or trailing spaces as it's a numeric data type. Only strings can have leading or trailing spaces.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1507354
Posted Tuesday, October 22, 2013 3:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 3,105, Visits: 11,498
Casting the float to a VARCHAR value is what causes the rounding you are seeing:
select
a.MyFloat,
ToVarchar = convert(varchar(30),a.MyFloat),
ToLtrimRtrim =ltrim(rtrim(a.MyFloat))
from
( Select MyFloat = convert(float,10041.25E00) ) a

Results:
MyFloat                ToVarchar                      ToLtrimRtrim
---------------------- ------------------------------ -----------------------
10041.25 10041.3 10041.3
Post #1507398
Posted Wednesday, October 23, 2013 12:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
GilaMonster (10/22/2013)
Why are you trimming a float in the first place?
A float cannot have leading or trailing spaces as it's a numeric data type. Only strings can have leading or trailing spaces.


Hi David,
What are you trying to do with trim function on FLOAT value?
Post #1507467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse