Numeric Data Type Precision (Rhetorical Question?)
Apr 12, 2007
I'm reading the MS Training Kit for SQL 2005 Admin, Exam 70-431, (no debating certs, please), and I come across this gem, discussing numeric data types such as bigint, int, smallint, decimal, numeric, etc...
"The storage is also precisely defined, so any data stored in these data types returns and calculates to the same value on either an Intel or an AMD processor and architecture."
So exactly how precise would you have to be to notice a difference between architectures?
i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.
the problem is when this numeric field is > 0 it looses the precision on its decimal value.
example, if numeric value is 0.32
after converting this to a string, the new value will be : .32
it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.
when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
I'm getting the above when trying to populate a variable. The values in question are : @N = 21 @SumXY = -1303765191530058.2251000000 @SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right When I try the following ie to populate a variable with that value I get the error - SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
I receive patient demographic files from hospitals that are in several different formats. I have written translations for each format. I need to upload the files into our accounting software. I have the file layout to upload data and it looks like this.
From To Length Record Type Code 1 2 2A Account Number 3 17 15A Guarantor Name 18 47 30A Guarantor Zip 125 129 5N Guarantor Area Code 134 136 3N
In SQL Server I have not found a way to set precision on an int. I have to have the correct length, and data type (A for alfa which is left justified and N for numeric which is right justified) field for a succsessfull upload. Suggestions on what data types to use would be very helpful, and then suggestions on how to output the data in a text file described example above would be a life saver.
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Our shop recently upgraded to MS SQL 2005 server from the prior SQL 2000 product.
I receive and error during processing related to inserting a value into a field of datatype real. This worked for years under MS SQL 2000 and now this code throws an exception.
The exception states:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 15 ("@TEST"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.
This error is caused by inserting several values that fall outside of a range that MS SQL 2005 documentation specifies.
The first value that fails is 6.61242e-039. SQL Server 2005 documentation seems to indicate that values for the datatype real must be - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
Why doesn't 6.61242e-039 just default to 0 like it used to?
I saw an article that might apply, even though I just use a C++ float type and use some ATL templates.
Is my question related to this post?http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201636&SiteID=1
Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin
I've a SQL view performing a number of very precise calculations ie.495/(1.112 - (.00043499*((Col1 + Col2 + Col3))) + ( .00000055 * ((Col1+ Col2 + Col3)) * ((Col1 + Col2 + Col3))) - (.00028826 * Col4))) -450)/100Obviously this sometimes causes a precision issues because the floatand real datatypes don't hold precise values... My problem is thatconverting to the numeric or decimal datatype easily produces an errorif the precision is not big enough to hold the resulting output... Sohere's my questions:Does anyone see a problem with me maxing out the precision of thedecimal datatype (ie. use DECIMAL(38,8) across a couple dozeninter-related formulas?So the above code would now exec like this:495/(CONVERT(DECIMAL(38,8),1.112) -(CONVERT(DECIMAL(38,8),.00043499)*((Col1 + Col2 + Col3))) +(CONVERT(DECIMAL(38,8),.00000055) * ((Col1 + Col2 + Col3)) * ((Col1 +Col2 + Col3))) - (CONVERT(DECIMAL(38,8),.00028826) * Col4))) - 450)/100If anyone does see a problem with this approach, could you suggestanother alternative?
the situation is that i have a column data comes from flat file and all i want to do is to check that the incoming column is numeric(12,3) and if the incoming data exceed that size "12,3" exception or redirect the row is happened.
the problem that i try to apply that with the data conversion or Derived column component but it in case of the scale of the incoming data exceed 3 the component trim until 3 scale.
i also try to perform it with the flat file data source component but i face a problem that if the data in the column is empty then flat file data source component read the numeric column as Zero
i hope someone help me coz i need to handle it soon.
Hi! Every one, I need to increase my numeric digit in a table.previous column name was "Amount",Precision=18 and scale=6, when I increase Precision=28 it show "Airthmatec Error", even when increase precision in a new table it work properly.
Hi all, I wanna know about, r Sql Server 2000 support the Booleon data type, if yes plz tell how can it use... and also tell about some common Data type. Thanx in advance Sajjad
I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error converting data type varchar to numeric".
Code: select DEPARTMENT, CASE WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2))) ELSE '' END AS 'JUNE', CASE WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2))) ELSE '' END AS 'JULY' from L27_PHAROS_DETAIL_DATA WHERE Department = 'TECHNOLOGY SERVICES' GROUP BY DEPARTMENT, DATE ORDER BY DEPARTMENT
I try to JOin to tables: Enterprise has a decimal(5,3) and COHIER has varchar 5. All I am getting is an error on converting data. How can I fix this problem.
SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description FROM dbo.COHIER a INNER JOIN dbo.Enterprise b ON a.Security_Value = b.Enterprise_Number
I am using MS SQL Server 2008R2 along with VB 2010.The first question is: why is it even trying to convert anything to numeric? I have NO numeric data types. And I don't have any nvarchar data types either. I'm very confused.Doesn't nchar include any and all characters, in any combination? Should I change everything to text data type? Maybe something else? Some values are going to be blank. The Lab/Source Lots have numbers, letters and dashes.
Yes again a topic like this, I couldn't get an answer for my problem so I started a new topic.
Im getting this error for these lines:
$registreer = "INSERT INTO Gebruikerstelefoon (gebruikersnaam, telefoonnummer, volgnr) VALUES ('".$_SESSION['gebruikersnaam']."', '".$_POST["telefoonnummer"]."', '')"; mssql_query($registreer) or die("Fout bij toewijzen telefoonnummer.");
'telefoonnummer' (phonenumber) is nummeric, users can insert their phonenumber in a input field (text) and register along.. ( this is a second query for the column phonennumber) though this doesn't work..
I am having difficulty with my sql and I am not sure what do to. I am new to this. Any help you can give would be helpful.
Here is the statement that is giving me trouble. If I take this out of my Sql query everything runs fine.
CASE clm_att1 WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100) WHEN 'NA' THEN '0.00' WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65 ELSE '' END as AccessFeeFinal,
CLM_H30 = Case When CLM_H30 = 0.00 Then clm_sppo else clm_H30 End,
clm_prod = CASE WHEN CLMS_sku = 'NEGO' THEN 'NEG' WHEN CLMS_sku = '% OF CHGS' THEN 'NEG' ELSE clm_prod End,
"clm_nego" = CASE WHEN CLM_ATT1 = 'NA' THEN 0.00 WHEN CLM_ATT2 = 'NA' THEN 0.00 WHEN CLM_ATT3 = 'NA' THEN 0.00 WHEN CLM_ATT4 = 'NA' THEN 0.00 ELSE clm_nego END,
"clm_sppo" = CASE WHEN CLM_ATT1 = 'NA' THEN 0.00 WHEN CLM_ATT2 = 'NA' THEN 0.00 WHEN CLM_ATT3 = 'NA' THEN 0.00 WHEN CLM_ATT4 = 'NA' THEN 0.00 ELSE clm_sppo END,
clm_1e, clm.CLM_ATT1 as Note,
CASE clm_att1 WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100) WHEN 'NA' THEN '0.00' WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65 ELSE '' END as AccessFeeFinal,
CLM_ATT2, CLM_ATT3, clio_fee04 as "ACCESSFEEIMPACT",
"(clm_sppo/CLM_TCHG) * 100" = CASE WHEN CLM_ATT1 = 'NA' THEN 0.00 WHEN CLM_ATT2 = 'NA' THEN 0.00 WHEN CLM_ATT3 = 'NA' THEN 0.00 WHEN CLM_ATT4 = 'NA' THEN 0.00 ELSE (clm_sppo/CLM_TCHG) * 100 END,
clio_wrk1 as MAS90#
FROM dbo.clm clm Join dbo.cli cli ON clm.clm_clir = cli.cli_id1 JOIN dbo.clip clip ON cli.cli_id1 = clip.clip_id1 JOIN dbo.clio clio ON cli.cli_id1 = clio.clio_id1 INNER JOIN dbo.clms clms ON clms.clms_id = clm.clm_id1
WHERE (clip.clip_prd IN ('NEG', 'ADV')) AND (clm_prod = 'ADV' OR clms_sku = 'NEGO' OR clms_sku = '% OF CHGS')AND (CLM_STADES IN ('DONE','DUPO','DUPL'))and clm_adjto = '' AND CLM_TCHG > 0. and (clio.clio_type = 'AC')
I am having problems with a statment that I am writing. The prop_uac is a alpha field and prop_disc is a numeric field. What I was trying to do is when the alpha field is not populated automatically pull from the numeric field. Then I wanted to put all of the results into one column. Can this be done? below is my statement. I hope you can help me.
Prop_uac = CASE WHEN prop_uac = '' THEN prop_disc else prop_uac End
hello everyone... i have some problem to with my trigger.. after execute the trigger.. i got this errro "Msg 8114, Level 16, State 5, Procedure trg_InsertPVReadingMeter, Line 14 Error converting data type varchar to numeric."
below is my command sql table PVReadingMeter
quote:USE [PVMC Database] GO /****** Object: Table [dbo].[PVReadingMeter] Script Date: 03/25/2008 01:18:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PVReadingMeter]( [PV_reading_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PVReadingMeter_PV_reading_id] DEFAULT (newid()), [PV_reading_date_time] [datetime] NOT NULL, [PV_reading_meter] [decimal](8, 2) NOT NULL, [PV_normalized_monthly_yield] [float] NOT NULL, [PV_normalized_monthly_energy_yield] [float] NOT NULL, [PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PVReadingMeter_PV_application_id] DEFAULT (newid()), CONSTRAINT [PK_PVReadingMeter] PRIMARY KEY CLUSTERED ( [PV_reading_id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO USE [PVMC Database] GO ALTER TABLE [dbo].[PVReadingMeter] WITH CHECK ADD CONSTRAINT [FK_PVReadingMeter_Photovoltaic] FOREIGN KEY([PV_application_id]) REFERENCES [dbo].[Photovoltaic] ([PV_application_id]) ON UPDATE CASCADE ON DELETE CASCADE
below is my trigger
quote:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
CREATE Trigger [trg_InsertPVReadingMeter] on [dbo].[PVReadingMeter] for insert as begin
SELECT @PVReadingMeter = (SELECT PV_reading_meter + '' FROM Inserted) SELECT @PVNormalizedMonthlyYield = (SELECT PV_normalized_monthly_yield + '' FROM Inserted)
-- Print the name of the new author
PRINT 'The new photovoltaic reading meter"' + @PVReadingMeter +'" is added.' PRINT 'The photovoltaic normalized monthly yield is "' + @PVNormalizedMonthlyYield +'"'
end;
now, this is command to insert the values into PVReadingMeter table using trigger that has been created....
how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose? if i select int64, it keep giving me an error: Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
there is no numeric data type in variable..
if you click the drop down box in variable data type, you can only see the below data type:
Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.
Here is the syntax that I am currently using:
Code Snippet SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date] FROM dbo.F_Report_Data AS a WHERE (szF3 <> 'Blank')
When I open the view I get the error message about converting varchar to numeric.
I have always programmed my ASP pages with MS Access DB's and we recently got a SQL Server 2000. So, I'm totally new to SQL Server 2000. I trying to retrieve data from a table in SQL, based on values from two input fields (text fields) on a form in the web page.
To process the form, I am using the following syntax:
Set oRs = oConn.Execute("SELECT BBILNAM, BTWP, BTXP, BPARCEL, BBILADD, BBILCTYST, BBILZIP5 FROM TESTdb WHERE BTWP = 'TWP' AND BPARCEL = 'PARCEL'")
SQL Fields in Table TESTdb are (BTWP = numeric field) and (BPARCEL = text field)
I realize that the problem is that I'm comparing a numeric field in my table, to a text field from my form, but don't know how to convert the text field to a numeric field, prior to the execution of the select statement. I'm hoping someone can point me in the right direction. Thanks!
