question

John B avatar image
John B asked

Convert Money to varchar with no rounding

Hi All I need some help, this is my first post.

I have this: 3416.65

I need to end up with this: 0341665

What I get when I try to cast(amount as Char(7)) is 3417

I can't figure out how to stop the rounding, I've tried varchar etc...

I'm ok with casting the left zeros, but how can I make the number appear correctly in my fixed file format.

Thanks, John B

sql-server-2005convertround
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered 
            
DECLARE @mnyAmount money,            
        @strAmount varchar(20)            
SELECT @mnyAmount = 3416.65            
            
SELECT CONVERT(varchar(20), @mnyAmount, 2)            
            
SELECT CONVERT(varchar(20), @mnyAmount * 100, 2)            
            
SELECT @strAmount = CONVERT(varchar(20), @mnyAmount * 100, 2)            
            
SELECT LEFT(@strAmount, LEN(@strAmount) - 5)            
            
SELECT RIGHT('0000000' + LEFT(@strAmount, LEN(@strAmount) - 5), 7)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.