Converting Strings to SmallMoney Data Type

[rwiethorn]

I'm trying to convert a string to a money(or smallmoney) data type.
The original string from the data source looks like this: -00000010232
(this would be -102.32 dollars), or 00000010200 (this would be 102.00
dollars. When I convert using the CAST method, the Cents drop off. The
'102.00' becomes '102'. Its a number now, but its missing the cents.

Another example is here: if the string parsed out is: .00 (meaning no
amount) then I get '0' as the money amount. I lost the zero cents.

I need help in keeping the cents if there is Zero values.

I use string functions to extract the data and append the 'sign' the
string. The first example is a negative dollar amount, and the second
one is a postive dollar amount with no cents.

Here is my code I used to parse the string (I used UDF's):

RETURNS varChar(15) AS --Size of the Field Name

BEGIN
--Declare the Variables
Declare @Results Money,
@Results varChar(15),
@strSign Char(1),
@strDollars varChar(12),
@strCents Char(2),
@CurValue varchar(15),
@NewLength tinyint,
@pos tinyint,
@TestString varchar(12)

--Parse out the Dollars for Clean-up
Set @TestString=substring(@strField, 82, 9)
--Set up a counter
Set @pos=1

While substring(@TestString, @pos, 1) ='0'--Will Test True until No
Zero is found in the Dollars Substring
Begin
--A Leading Zero is found in postion 1 of the Dollars SubStrin
--Increment the Counter again and check the next Character for a
Zero
Set @pos=@pos+1
End
Set @NewLength= len(@TestString)-(@pos-1)--The Orginal String is now
shorter without the Leading Zeros
Set @strDollars=substring(@TestString, @pos, @NewLength)

--Now Get the Sign, and the Cents
SET @strSign = (Substring(@strField, 81, 1))
SET @strCents = (Substring(@strField, 91, 2))

Set @Results= @strSign + @strDollars + '.' + @strCents

Return @Results

Things I've tried: I used the Cast Method on the last line Set
@Results=CAST (@strSign + @strDollars + '.' + @strCents as SmallMoney)

This will cause a problem.

I also tried applying the CAST method to the individual variables,
@StrDollars, @strCents, but that gives me the same results.

Any help in the matter, or suggestions for things I should try would
be great.

Thanks,
rwiethorn

How about this:
select cast('-00000010232' as smallmoney)/100

"rwiethorn" <rwiethorn2002@yahoo.com> a écrit dans le message de news: [email]553a0349.0307020549.6fe77f43@posting.google.com[/email]...

> I'm trying to convert a string to a money(or smallmoney) data type.
> The original string from the data source looks like this: -00000010232
> (this would be -102.32 dollars), or 00000010200 (this would be 102.00
> dollars. When I convert using the CAST method, the Cents drop off. The
> '102.00' becomes '102'. Its a number now, but its missing the cents.
>
> Another example is here: if the string parsed out is: .00 (meaning no
> amount) then I get '0' as the money amount. I lost the zero cents.
>
> I need help in keeping the cents if there is Zero values.
>
> I use string functions to extract the data and append the 'sign' the
> string. The first example is a negative dollar amount, and the second
> one is a postive dollar amount with no cents.
>
> Here is my code I used to parse the string (I used UDF's):
>
> RETURNS varChar(15) AS --Size of the Field Name
>
> BEGIN
> --Declare the Variables
> Declare @Results Money,
> @Results varChar(15),
> @strSign Char(1),
> @strDollars varChar(12),
> @strCents Char(2),
> @CurValue varchar(15),
> @NewLength tinyint,
> @pos tinyint,
> @TestString varchar(12)
>
> --Parse out the Dollars for Clean-up
> Set @TestString=substring(@strField, 82, 9)
> --Set up a counter
> Set @pos=1
>
> While substring(@TestString, @pos, 1) ='0'--Will Test True until No
> Zero is found in the Dollars Substring
> Begin
> --A Leading Zero is found in postion 1 of the Dollars SubStrin
> --Increment the Counter again and check the next Character for a
> Zero
> Set @pos=@pos+1
> End
> Set @NewLength= len(@TestString)-(@pos-1)--The Orginal String is now
> shorter without the Leading Zeros
> Set @strDollars=substring(@TestString, @pos, @NewLength)
>
> --Now Get the Sign, and the Cents
> SET @strSign = (Substring(@strField, 81, 1))
> SET @strCents = (Substring(@strField, 91, 2))
>
> Set @Results= @strSign + @strDollars + '.' + @strCents
>
> Return @Results
>
> Things I've tried: I used the Cast Method on the last line Set
> @Results=CAST (@strSign + @strDollars + '.' + @strCents as SmallMoney)
>
> This will cause a problem.
>
> I also tried applying the CAST method to the individual variables,
> @StrDollars, @strCents, but that gives me the same results.
>
> Any help in the matter, or suggestions for things I should try would
> be great.
>
> Thanks,
> rwiethorn