Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Question Unanswered: Money type confusion

    I need a little advice for the Money datatype. When entering a value like $18.20 into a column of datatype Money, it stores and returns a value of 18.2 . It is set to the default paramaters. How can I change this so that it is correctly displayed as $18.20. My results are printed onscreen through VB, is that where I need to reformat?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure it's money?

    Code:
    DECLARE @x money
    
    SELECT @x = 18.20
    
    SELECT @x
    EDIT: It's a presentation layer issue...

    Then There Also


    SELECT CONVERT(varchar(15),@x,1)
    Last edited by Brett Kaiser; 11-18-03 at 16:54.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    7
    Positive it's Money

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's the sql being executed from? a stored procedure or called from a front end?

    Did you try the code I posted in QA?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    7
    It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.

    blindman

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you know how to script the table so we can see the DDL?

    Did you use the convert from the page?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Posts
    7
    I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.

    And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?



    Originally posted by Brett Kaiser
    Do you know how to script the table so we can see the DDL?

    Did you use the convert from the page?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT CONVERT(varchar(15),@x,1)

    Just replace the @x with the column name

    Do you have SQL Server client tools installed on your desktop?

    Do you know what Enterprise Manager is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Actually you should make the varchar(25) to handle all possible money values...

    money and smallmoney
    money

    Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

    smallmoney

    Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    [i]Actually you should make the varchar(25) to handle all possible money values...


    quote:
    --------------------------------------------------------------------------------

    money and smallmoney
    money

    Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

    smallmoney

    Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

    --------------------------------------------------------------------------------
    Brett ....
    That sounds like an awful lot of Money. Hope the person having so much can lend some to me
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Nov 2003
    Posts
    7
    HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.

    And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25

    922 Trillion Dollars....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Nov 2003
    Posts
    7
    Thanks for you help, I should be able to manage from here

  15. #15
    Join Date
    Nov 2003
    Posts
    7
    If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.

Posting Permissions

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