[RESOLVED] transforming numeric amount

[Golfreak]

Hi all,

I am trying to transform a numeric amount in SQL Server 2000. What I started with is this:

-119.760000000000

What I have now is this:

-119.76

What I need when I am done is this:

0000011976

I know there is a function or method to accomplish my goal.
Can anyone help?

[szlamany]

What is the actual datatype in SQL?

(didn't like my last solution - eh?)

[szlamany]

Here's a function - I'm assuming a MONEY datatype - you can change it to whatever you want.

Create Function dbo.FormatNumber (@InVal money, @OutLen int) Returns varchar(100)
As
Begin
    Return Left(Right(Replicate('0',@OutLen+1)+Cast(Abs(@InVal) as varchar(100)),@OutLen+1),@OutLen-2)
    +Right(Cast(Abs(@InVal) as varchar(100)),2)
End

Use it like this:

Select dbo.FormatNumber(-119.76,10)

Returns this:

0000011976

[Golfreak]

Thanks again szlamany,

But for some reason it doesn't work properly for me. I copied it just like you have it except I changed it slightly to fit my needs.
I changed:
'MONEY' to "NUMERIC'
(that is the data type I am trying to return from the DB)
and I changed:
'dbo.FormatNumber(-119.76,10)' to 'dbo.FormatNumber(b.PaymentAmountCur,10)'
because that is the field from the DB that needs formatting.
I say it is not working properly because this is what I need:

0000011976

And this is what I get when I run the function in the Query analyzer:

0000000020

Just a side note- I found a way using what you gave me, another function I came up with, and just brainstorming and came up with this result:

00000119760000000000

This is the code I used:

Right('0000000000'+dbo.StripDecimalPoints(CONVERT(VARCHAR(20),B.PaymentAmountCur)),20)

and this is my StripDecimal function:

CREATE FUNCTION dbo.StripDecimalPoints (@amt varchar(20))
RETURNS varchar(20) AS  
BEGIN 
    declare @pos int
    set @pos = patindex( '%[ -. ]%' , @amt)
    while @pos > 0
    begin
        set @amt = replace ( @amt, substring( @amt, @pos ,1), '')
        set @pos = patindex( '%[ -. ]%' , @amt)
    end
    return rtrim( @amt)
END

Any suggestions?

[szlamany]

I've never used NUMERIC datatypes - and you aren't giving me the (X,Y) sizing of them - that's what's causing your problem

Try my function as I gave it to you - but...

select dbo.FormatNumber(Cast(b.PaymentAmountCur as money),10)

btw - why are you not using MONEY datatype?

If that doesn't work give me the (x,y) size of the numeric so I can code for it.

[Golfreak]

OK,

I tried your suggestion and it yielded the same results as before.

The table that PaymentAmountCur is coming from has it set up like this:
data type - Numeric
length - 13 (but this is confusing because it prints out as many as 20 chars)

The reason why I can't change it is because it is a table in Microsoft Axapta and for some reason it needs to be set up the way that it is.

[szlamany]

OK,

I tried your suggestion and it yielded the same results as before.

The table that PaymentAmountCur is coming from has it set up like this:
data type - Numeric
length - 13 (but this is confusing because it prints out as many as 20 chars)

The reason why I can't change it is because it is a table in Microsoft Axapta and for some reason it needs to be set up the way that it is.

Isn't there a length and decimal precision to a NUMERIC datatype? At least there is in MSSQL...

I'm off to dinner - will work with you later if you still need assistance...

[Golfreak]

Yeah me too...

Thanks for all your help szlamany, I am heading home, but I'll be back tomorrow unless I have a revalation tonight.

Have a good one.