> 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 pd 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 p the string (I used UDF's):
> RETURNS varChar(15) AS --Size of the Field Name
> --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)
> --P 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
> --A Leading Zero is found in postion 1 of the Dollars SubStrin
> --Increment the Counter again and check the next Character for a
> Set pos=pos+1
> 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.