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: Thursday, July 24, 2014 9:18 AM
Points: 92, Visits: 675
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 @ 8:33 PM
Points: 3,359, Visits: 7,271
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Thursday, July 24, 2014 9:18 AM
Points: 92, Visits: 675
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
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 @ 11:22 AM
Points: 3,122, Visits: 11,406
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