August 6, 2014 at 2:08 pm
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
Try using CAST or CONVERT
August 7, 2014 at 6:29 am
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
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply