[RESOLVED] transforming numeric amount

Hi all,

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

Quote:

---

-119.760000000000

---

What I have now is this:

Quote:

---

-119.76

---

What I need when I am done is this:

Quote:

---

0000011976

---

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

What is the actual datatype in SQL?

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

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

VB Code:

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

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:

Quote:

---

0000011976

---

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

Quote:

---

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:

Quote:

---

00000119760000000000

---

This is the code I used:

VB Code:

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

and this is my StripDecimal function:

VB Code:

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? :confused:

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.

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.

Quote:

---

Originally Posted by 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.

---

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...

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.