Professional Web Applications Themes

Converting Strings to SmallMoney Data Type - Microsoft SQL / MS SQL Server

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

  1. #1

    Default Converting Strings to SmallMoney Data Type

    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

    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)

    --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
    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
    rwiethorn Guest

  2. #2

    Default Re: Converting Strings to SmallMoney Data Type

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

    "rwiethorn" <rwiethorn2002> a écrit dans le message de news: [email]553a0349.0307020549.6fe77f43posting.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 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
    >
    > 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)
    >
    > --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
    > 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
    Guest

Similar Threads

  1. Converting from data type varchar to data type money
    By schaudry in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 10th, 11:01 PM
  2. Converting data type varchar to data type money
    By schaudry in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 10th, 07:22 PM
  3. Simple Question: Converting lists to strings
    By lingo smith in forum Macromedia Director Basics
    Replies: 6
    Last Post: April 17th, 04:04 PM
  4. <<Error converting data type char to smalldatetime>>
    By Gary D. Rezek in forum ASP Database
    Replies: 9
    Last Post: September 17th, 06:57 PM
  5. Converting DBM to other DB file type
    By Archi3 in forum PERL Miscellaneous
    Replies: 5
    Last Post: September 12th, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139