Data Types: DECIMAL Vs. MONEY
Aug 29, 2007
What significant difference is there between the two data types when we are indeed dealing with monetary values (other than being able to set the precision and scale)?
Is there a performance gain from one over the other? Are there administrative-related concerns that should force someone to choose one over the other? Are there any concerns of the MONEY data type being sunsetted by Microsoft anytime in the near future?
Can someone please help me out here?
[EDIT]
I was referring to "fields" in the first sentence when it should've been "data types".
[/EDIT]
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/
View 3 Replies
ADVERTISEMENT
Sep 26, 2007
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
View 5 Replies
View Related
Feb 9, 2008
what are the advantages of using the money data types over decimal etc?
View 6 Replies
View Related
Aug 13, 2015
I've got several columns in my database stored as money type. For my purposes, when reporting this data I need to truncate trailing zeros after the decimal. I know I can create a function to do this. I think given the quantity of columns and the numerous queries accessing them, I will undoubtedly forget to use a function everywhere needed.
I thought user defined types may be a solution. I've never used them before. I would still like to store the data as a money type, but anytime it is accessed it would be formatted. Can user defined types do this, or is there a better way?
View 18 Replies
View Related
Jul 26, 2013
On the Microsoft website, I am trying to understand the how the decimal and numeric data types work. It says that valid values for precision are:
- 10^38 +1 through 10^38 - 1
I don't understand the purpose of the negative sign before the first 10.I understand that decimal (p,s) refers to haing a total of p digits (before and after the decimal) and only s number of digits to the right of the decimal. How does the equation above relate to the 's' portion of the syntax?
View 2 Replies
View Related
Apr 4, 2006
I have a simple Integration Services project and the problem is that decimal fields are importing as real (I'm loosing the digits behind the decimal point).
The project contains a data flow task importing a flat file (.csv) to an SQL Server destination. My .csv file has two decimal type fields. Example:
Field 1: 12345.67
Field 2: .123456
My database table that I'm importing to has two fields. The only way that I can get this data to import is to define the fields as "float" in both the text file and database table. I want the database table fields to be defined as decimal or numeric, not float:
Field 1: decimal(7,2)
Field 2: decimal(6,6)
When all fields are defined as decimal (in both the flat file and database file), I get the following results:
Field 1: 12345.00
Field 2: .000000
How does one import decimal data from a flat file (.csv)?
Thank you in advance!
View 1 Replies
View Related
Aug 27, 2004
Could someone explain to me what the best practices are for using these three data types? (i.e. when to use them). I thought this would be a simple answer to find, but after looking through books on line, and this forum, I have still not found the answer. Can anyone help?
View 1 Replies
View Related
Oct 26, 2004
I have a table with a money field that had previously been running calculation and storing the data into the database's money field. Since this field supports 4 decimal places, it was storing 4 decimal places worth of data. I have since cleaned up my insert routine to round everything up to two decimal places and it only inserts the rounded values. I now have to go back and update the old data with the two decimal place rule. How would I go about doing this?
OLD---------------------------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16
View 7 Replies
View Related
Sep 6, 2007
Hi
I have an SQL search that is converting two values to type money. I want it to show two digits after the decimal point but am getting inconsistent results. The first value is as follows:
tblInventoryItem.itemcost as originalcost (the column is datatype money)
This displays correctly i.e. 2000.00 or 150.70 etc
The second value is this:
tblInventoryItem.itemcost + tblUpgrades.ItemCost as totalcostincupgr (both columns are datatype money)
But this displays as 2000 or 150.7
How can I get the second value to show two decimal places even when the digits are zeros?
Thanks!
View 3 Replies
View Related
Oct 30, 2007
Hi,
The default number of decimal points for 'money' data type is 4. Can I change it as 6?
Eg 120.123456
Thanks!
View 7 Replies
View Related
Nov 16, 2007
I have a field in a database which is a datatype Money. When I run a select query the data is coming back with 4 decimal places like 100.0000 but I only want 2 decimal places like 100.00.
Anyone know how to get this?
macca
View 12 Replies
View Related
Dec 27, 2007
Please Help me ...
How to set Money datatype decimal field with example .
View 5 Replies
View Related
Mar 29, 2007
I have a table in SQL 2005 with a field that has a value of type 'money'. When values are added, the field has 4 decimal places. Is there a way that I can make it only have 2 decimal places right away? Thanks!!!
View 1 Replies
View Related
Mar 24, 2008
How do I format the money or float field types to 2 decimal places during a SELECT statement?
View 4 Replies
View Related
Jan 16, 2013
What is the difference between Money and (Float or Decimal) Datatype. If we use Float or Decimal instead of Money, will we loose any functions..?
View 4 Replies
View Related
Mar 28, 2008
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
View 2 Replies
View Related
Oct 20, 2007
Hi,
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped:
[Department, DEP_CLEGALCODE]
One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
thanks.
View 6 Replies
View Related
Dec 8, 2013
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies
View Related
Jul 24, 2006
Hello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
View 6 Replies
View Related
Oct 16, 2007
I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.
View 9 Replies
View Related
Jul 15, 2004
Hi,
I have a Price column which has 'money' as the data type.
Then I populated some data into the table. I enter '1.00' in the Price column, then I used the following code to get the data:
Label2.Text = "$" + dataSet1.Tables["products"].Rows[0]["price"].toString();
However, the price is displayed as "$1.0000". But I believe it should display "$1.00". So how can I get rid off the two zeros at the end.
regards
View 5 Replies
View Related
Oct 6, 1999
Why does the money data type have 4 decimal places eg £134.3453 or $12.3636
I would have expected it to only have 2.
View 2 Replies
View Related
Jul 28, 2007
hi
When we use money data type and which type data
thanks
kunal
View 1 Replies
View Related
Jun 1, 2006
I choose a price field as a money data type, but I cannot change thescale of this.the default scale is 4 (it dimmed).how can I change to 2? I only need 2 scale such as $23.33 instead of$23.3344
View 3 Replies
View Related
May 12, 2005
Hi,
I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code:
' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) '
Will this work? Is there any reason I should stay away from the "Money" data type?
Thanks,
David
View 3 Replies
View Related
Mar 23, 2006
Why does SQL add 4 zeros at the end of a money data type? I have to format my strings once they are retrieved because of this. I am not sure if I did something wrong, but shouldn't it only have 2 trailing zero's?
View 1 Replies
View Related
Jul 17, 2001
Hi, How would I convert(or format) money data type, so the output will be like: 123,456.78 or 12,345.67
(In other words how would I insert a comma which separates hundred from thousand...)
Thanks a lot,
Andy
View 1 Replies
View Related
Jun 7, 2003
I'm using the money data type in a field - but (obvious to me) I need the data to contain two decimal places NO MATTER WHAT - even if they are zeros!
I keep putting in "5.20" and I get "5.2".
How do I keep this from happening? I need both decimal places! I thought about using a text field - but that seems to be wasteful and I would have to do a type cast to do numeric computations.
There has to be a way to do this - this IS the purpose of the data type, isn't it??
Thanks for any help!!
Ryan
View 11 Replies
View Related
Sep 15, 2005
Hi Guys,
We are looking for advice on what to do here; we started out with our db holding ex VAT pricing now. Now the problem is the money type can only hold a few decimal places so when we are converting prices to incl vat it can't actually output the correct pricing and it comes our very ugly.
Unfortunately there is no bigmoney data type so we are in a bit of an awkward position
Any idea's?
View 1 Replies
View Related
Jun 4, 2004
Hi All,
Is there a way to set a MONEY datatype to a scale (decimal places) of 2?
The default is set to 4 and I can't seem to find any resources on how to change it.
Do I have to add a check constraint to manually round to 2 decimals??
That seems unneccessary.... but if it is, boo-urns to sql server.
thanks!
View 1 Replies
View Related
May 11, 2000
I am trying to do some amount calculations.
The amount was declared as nvarchar data type.
I did the following coding. I want to get 2 places right of decimal
but am getting only one place right of decimal.
SELECT
(CAST(EQ_TotQuoteAmnt AS Float(7,2)) -
CAST(EQ_InsFee AS Float(7,2)) - 150) as Inforce_Premium
FROM Quoted
Can someone please help me with the syntax?
Thanks in advance.
M. Khan
View 1 Replies
View Related
Nov 23, 2014
When i try to use the following command;
Update (table name)
set (Field name)='0'
where ID='1449' (1449 location of the record).
The "Field name" value is 875 and i need to edit the value to 0(zero).
It appears an error -
----------------------------
Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.table name', column 'field name'. Use the CONVERT function to run this query.
-----------------------------
So if i change the data type from right click design menu to nvarchar(50) can it be edited from the command that i used above?
View 3 Replies
View Related
Oct 4, 2006
How to display money type data properly, with commas every 3 digits?
I've tried :
CONVERT (money, ProjectCost, 1) AS ProjectCost
but it gives me the type as 1234.56 no commas in it?
can anyone help me with that?
thanks!
View 3 Replies
View Related
