Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
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
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.
hi
When we use money data type and which type data
thanks
kunal
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 RelatedHi,
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
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 RelatedHi, 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
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
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?
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!
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
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!
what is the recommended data type i should use if i want to have a price field that can include "TBA". i can't use smallmoney i suppose, so i should use VARCHAR then validate the String with Visual Studio?
View 1 Replies View RelatedI have some data that I am outputting to a text file:
sum(sales)
where sales is datatype money. If I execute the query with query analyzer I get 4 decimals, but when it is output to a text file it rounds the number
Grid Text File
2182035.9600 2182035.96
961799.2400 961799.24
22104768.1850 22104768.185
If I do cast or convert it to money it does the same thing.
It really doesn't make a material difference for its intended use but I would like to keep it consistent with 4 decimals.
Hi all!
I want to create a table. One of the columns should be in the data type MONEY with two digits on the right side of the decimal point.
How is that possible?
Thanks a lot and greetings from vienna
landau
How to display money type data with commas every 3 digits?
I 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!
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?
In my database there is a big table and format is something like this:
CREATE TABLE [dbo].[MyTable](
[aaa] [uniqueidentifier] NULL,
[bbb] [uniqueidentifier] NULL,
[ccc] [nvarchar](max) NULL,
[ddd] [nvarchar](100) NULL,.......etc.........
There are some more columns with more 'nvarchar' (max) and other INT data types. Anyway, I know a page is 8K size. How do I find out how much space does A ROW takes with above datatypes? If users add 5000 Rows per day, how do I figure out how much size the table will increase?
I'm using the SQL below to work out recruitment figures. The query works well, but I now need to build this query into a DotNet application as a webpage. I therefore need to declare and manipulate the values such as 'Total', 'Jan' to 'Dec' and 'YearRecruited' in order to write these values into my chosen data control. I therefore need to know the datatypes of these calculated values.
Total I think is straightforward as DNAScreeningID is an int datatype. But what about the others?
SELECT YEAR(SamplingDate) AS YearRecruited,
COUNT(CASE WHEN month(SamplingDate) = 1 THEN DNAScreeningID END) AS 'Jan',
COUNT(CASE WHEN month(SamplingDate) = 2 THEN DNAScreeningID END) AS 'Feb',
COUNT(CASE WHEN month(SamplingDate) = 3 THEN DNAScreeningID END) AS 'Mar',
COUNT(CASE WHEN month(SamplingDate) = 4 THEN DNAScreeningID END) AS 'Apr',
[Code] ....
I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)
The first question is does the XML data type have a size limitation? For example do you do something like:
ConfigFile XML(1000) NULL
Or is it just something like this:
ConfigFile XML NULL
The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD.
I am having an issue fetching Chinese characters in a XML data type. It return questions mark (?).
Below is the sample script.
DECLARE @XMLVAR XML
SET @XMLVAR = '<?xml version="1.0"?>
<POLICY_SEARCH xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<NAME>QA*保险1</NAME><NUMBER /></POLICY_SEARCH>'
SELECTI.xmlParam.query('./NAME').value('.','NVARCHAR(25)') NAME
,I.xmlParam.query('./NUMBER').value('.','NVARCHAR(25)') NUMBER
FROM@XMLVAR.nodes('POLICY_SEARCH') AS I(xmlParam)
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?
As per my requirment using below script to convert from varchar to numeric.
declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)
Getting error : Error converting data type varchar to numeric
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 RelatedI have problem to execute query with interval date.
If try this query I don't have problem and the output is correct:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);Instead if try this I have error:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);
[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
When I run the following sql query:
"update table set price = price * 1.1 "..
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
Hi,
I created a function class in "Script Components" to convert an Integer to a Date data type. It works good except that it converts NULL value to the date 1988-12-31; but I want to return NULL when the value is NULL. Any idea on this?
Here is my function:
Public Class ScriptMain
Inherits UserComponent
Private Function ConvertToDate(ByVal dateIn As Double) As Date
Dim DateStr as String
...........
...........
If DateStr Is Nothing Then
Return Nothing
Else
Return CDate(DateStr)
End If
End Function
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.ReportDte = ConvertToDate(Row.RPRTDT)
End Sub
End Class
hi,
when I use the expression
1==1? 5:12
in the derived column expression it works fine, but when I try to make the "else return"=null I get a parsing error
I am using the syntax that Jamie posted but I don't know how to write the value for the null part and what should (for example) in the <data-type> place
thanks,
Aref
so i have a table with 25million rows. this table has an image data type column and i want to set this column to null. what is the most efficient way to archive my goal. SQL SERVER 2008
View 3 Replies View RelatedWhat is the syntax to pass in null value for boolean data type in VB in the stored procedure?
This is what I tried and it doesn't work.
.Parameters.Append .CreateParameter("@disposition", adBoolean, adParamInput, 1, vbNull)
Thank you.
Hi, I have a record set that is bound to a table in MS SQL Server. Onefiled in the table is bound to a "long" type member variable in theRecordSet. What will happen to the "long" variable when the field isNULL in the table?Thanks!-Yi
View 1 Replies View Related