Char Format

I have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

any suggestions on how to make this conversion?This is freaking blowing my mind...

This should work

DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, RIGHT(REPLICATE('0',11)+CONVERT(char(11),CONVERT(d ecimal(15,2),@.x)),11)

But's it's acting like it's doing an implicint conversion to numeric...

Hold on..

If I do...

DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, REPLICATE('0',11)+CONVERT(char(11),@.x)|||If you just need the rounding, then check the round (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_93z8.asp) function. If you need zero padding too (which is rather silly in most cases, since SQL Server handles all of the "leading zero" issues for you), then you'll probably have to resort to a user defined function.

-PatP|||Methinks thou meant:DECLARE @.x decimal(15,5), @.y char(11)
SELECT @.x = 800.75864
SELECT @.x, RIGHT(REPLICATE('0',11)+CONVERT(varchar(11),CONVER T(decimal(15,2),@.x)),11)That works, as long as the value to convert is positive... Then you get some "interesting" results as the sign floats about!

-PatP|||Yeah...what an idiot...

char(11)...

just found the prob...

Thanks...|||It is the "little" things that make the biggest boom when you trip over them!

-PatP|||declare @.n numeric(19,5)
set @.n = 800.75864
SELECT REPLACE(STR(@.n-.005,11,2),' ','0')

Hans.|||THANKS A LOT! IT WORKED|||Originally posted by diegocro
THANKS A LOT! IT WORKED

What did you expect?

And which one did you use?|||Both, I've been trying to do this since yestarday, that's why I was happy.|||Well that'll learn ya