November 18, 2014 at 12:30 am
Hi,
I am having two columns with the money datatype values.
Column1 Column2
$134,456 $15,897
$123 $78,556
But when try to sum these values I get an error message like this "Operand data type nvarchar is invalid for sum operator".
Really appreciate any help on this.
November 18, 2014 at 12:58 am
Can you post the query?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2014 at 1:43 am
and the table definition please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2014 at 3:53 am
First of all using money data type is not recommended because it is not precise. Instead use numeric column. You can always append currency symbol at UI level of reports.
Now that you are using the column, did you try CAST or CONVERT?
November 18, 2014 at 4:07 am
ksatpute123 (11/18/2014)
First of all using money data type is not recommended because it is not precise. Instead use numeric column.
Care to explain why money isn't precise?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2014 at 4:29 am
Disadvantages of money datatype:
It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?
Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.
It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.
The MONEY data type has rounding errors.
Ref:msdn
Details taken from: http://stackoverflow.com/questions/22343185/when-to-use-money-data-or-decimal-data-type-in-sql-server-to-store-costing-value
See also
I hope this clarifies your doubts.
November 18, 2014 at 4:41 am
ksatpute123 (11/18/2014)
Disadvantages of money datatype:It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?
Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.
It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.
This has nothing to do with "being imprecise".
You call writing code in dialect speaking like a hillbilly, I call it using the most efficient resources available (not saying MONEY is the most efficient, I'm talking generally).
ksatpute123 (11/18/2014)
The MONEY data type has rounding errors.Ref:msdn
Details taken from: http://stackoverflow.com/questions/22343185/when-to-use-money-data-or-decimal-data-type-in-sql-server-to-store-costing-value
See also
I hope this clarifies your doubts.
I don't see a reference to MSDN btw.
At the MSDN page about the money data type, I see the following:
The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.
This is not the same as "having rounding errors". If you encounter "errors ", it is most likely because you are going outside the defined precision (and decimal, int, numeric etc have the same "issues"). Or you are multiplying/dividing money with money, which makes no sense at all.
Almost all examples from the stack overflow thread fall into these two categories.
I'm not saying money is the best thing ever and it sure has it's issues, but just to go around declaring it should never be used is one step too far for me 🙂 It's OK to use it if you use it for what it is supposed to be used and if you know the limitations of the data type.
(Using money data type can have a good performance impact on the processing of SSAS cubes for example)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2014 at 4:52 am
Here is the MSDN reference:
I hope this helps in further clarification regarding money data types.
November 18, 2014 at 4:56 am
ksatpute123 (11/18/2014)
Here is the MSDN reference:I hope this helps in further clarification regarding money data types.
Great, so you just copy pasted a response from Celko. Now I certainly don't regard it as important 😉
Read the last reply in that MSDN thread as well, it has a more balanced view on the data type.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2014 at 4:57 am
I'm not saying money is the best thing ever and it sure has it's issues, but just to go around declaring it should never be used is one step too far for me 🙂 It's OK to use it if you use it for what it is supposed to be used and if you know the limitations of the data type.
(Using money data type can have a good performance impact on the processing of SSAS cubes for example)
I completely agree but most of the times we do have to do a lot data massaging/calculations and money data types are not very reliable for arithmetic operations due to their rounding off issues.
In case if we are talking about accuracy VS little performance hit. I say accuracy should be the winner.
November 18, 2014 at 5:03 am
Koen Verbeeck (11/18/2014)
ksatpute123 (11/18/2014)
Here is the MSDN reference:I hope this helps in further clarification regarding money data types.
Great, so you just copy pasted a response from Celko. Now I certainly don't regard it as important 😉
Read the last reply in that MSDN thread as well, it has a more balanced view on the data type.
Yes, I did copy paste and therefore mentioned the MSDN reference. I did so because I agree with his views. and as I pointed out in my previous reply, most of the times we do have to work with higher precision and more than just store, sum and average.
November 18, 2014 at 5:22 am
ksatpute123 (11/18/2014)
Here is the MSDN reference:
That's not an MSDN reference, that's a forum, it's no more or less reliable than any other forum. MSDN reference would be one to the documentation on MSDN - http://msdn.microsoft.com/en-us/library/ms179882.aspx
Money is accurate to the 4 decimal places that they store, if that accuracy is sufficient for someone's needs, fine. If not, then they need to use something with more accuracy, Appropriate data type for the job and all that.
Can we maybe stop the derail of the thread and help the OP with his conversion errors?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2014 at 5:24 am
GilaMonster (11/18/2014)
Can we maybe stop the derail of the thread and help the OP with his conversion errors?
Sure, once he replies back 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2014 at 9:10 am
ksatpute123 (11/18/2014)
I'm not saying money is the best thing ever and it sure has it's issues, but just to go around declaring it should never be used is one step too far for me 🙂 It's OK to use it if you use it for what it is supposed to be used and if you know the limitations of the data type.
(Using money data type can have a good performance impact on the processing of SSAS cubes for example)
I completely agree but most of the times we do have to do a lot data massaging/calculations and money data types are not very reliable for arithmetic operations due to their rounding off issues.
In case if we are talking about accuracy VS little performance hit. I say accuracy should be the winner.
You'll run into "rounding issues" with any data type if you don't pay attention to the interim values, etc... You'd have to be very clear on whether it's even appropriate to look at those extraneous digits past the stated precision of money: most of your inputs are approximations as well, so pretending like you can use 100 digits past the decimal point is a non-starter. Really no different from int of numeric for that matter (those "precise" types aren't any more accurate than float: the loss of precision, i.e. mathematical error, is just handled "for you" so we can pretend it isn't there).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2014 at 9:14 am
Koen Verbeeck (11/18/2014)
ksatpute123 (11/18/2014)
Here is the MSDN reference:I hope this helps in further clarification regarding money data types.
Great, so you just copy pasted a response from Celko. Now I certainly don't regard it as important 😉
Read the last reply in that MSDN thread as well, it has a more balanced view on the data type.
ROFL. I had assumed this was either a copy/paste or Celko created a new login here. That useless and aggressive diatribe could only come from one place.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply