Post reply

Help to add money value

  • August 6, 2014 at 2:08 pm

    #310550

    I am trying to add a field (bank_chk_amt] from a table in to my query to bring in a dollar amount....I originally had this below

    SELECT dbo.CBPAYMENT.REFERENCE_NO, 'CCMCCHBREF' AS Literal, RTrim([description]) + ', ' + [bank_chk_amt] + ', ' + convert(char(10),[check_date],101) + ', ' + 'Refund check sent to ' + [payee_name] AS [Free Text]

    FROM dbo.CBPAYMENT;

    but I would get "Error converting data type varchar to numeric".

    So my co-worker modified it and added (str([bank_chk_amt]) to my query which worked, but I noticed it dropped of the cents. So instead of 80.35 it would show 80 And I noticed it rounded 100.50 to 101

    how can I bring in the full dollar amount and without rounding?

  • August 6, 2014 at 2:38 pm

    #1735784

    Try using CAST or CONVERT

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 7, 2014 at 6:29 am

    #1735938

    I still have my training wheels for SQL, I never used those commands before. Can you please show me how I would use it?

    *I like your signature lol

  • August 7, 2014 at 8:23 am

    #1735996

    That's why I included a link to the help topic on those functions. Books online (BOL) or online help or simply SQL Server help (as easy as pressing F1 on Management Studio) explains how you can use any function available. Your capability to use it, will help you improve your skills.

    To convert your value from money or decimal into a string to concatenate it with the other values, you just need to follow the syntax. Note that convert offers you an additional parameter to format money (and other data types).

    Examples:

    CONVERT(varchar(15), [bank_chk_amt], 1)

    CAST( [bank_chk_amt] as varchar(15))

    Note that 15 is an arbitrary value that you must adapt depending on your real data and that the third parameter on CONVERT won't do anything with numeric, decimal or int types.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply