colA colB colA - colB The reminder
2.04166666666667 2 0.0416666666666665 0.999999999999996
You'll notice the workaround to get the decimal part of a number, if you wonder why its because sql2000 in the operand % only supports integers. Anyway, try on your calculator ( 2.04166666666667 - 2 ) it probably answer: 0.04166666666667.
BUT SQL NOT! you see how the result is amazingly 0.0416666666666665 ????
Does anybody knows a solution for this??? What am I doing wrong???
If you try the query but instead of 49.0 use 25.0, it returns the correct value !!.
Data is being trnasferred from an Oracle view to a SQL Server 2005 table.
Decimals can be previewed in the from the "SQL Command Text Window" but the columns in the target table which are defined as float shows the data being rounded to zero decimal places.
For the Data Source the always use default code page is selected.
I just want to calculate this formula in my report.
my code is as follows.
Code Block WITH CDsTotalOutstandingCTE AS ( SELECT type = 'CDs Total Out Standing' , TotalOutStandingValue = (select sum(Amount) from CertificateDeposit where State = 'Current')
), LoansTotalOutstandingCTE AS ( SELECT type = 'Loans Total Out Standing' , TotalOutStandingValue = (select sum(Amount) from Loan where State = 'Current') ),
combinedCTE AS ( SELECT * FROM CDsTotalOutstandingCTE UNION ALL SELECT * FROM LoansTotalOutstandingCTE
) SELECT *
FROM combinedCTE
Now I want to add another row to my table which would give Net Total Outstanding value.
It is calculated as follows.
Net outstanding value = TotalOutstandingValue(Calculated from CD table) - TotalOutstandingValue(Calculated from Loan table)
I am working with an off the shelf app that controls decimal places through the application. All columns underlying the apps 'numeric' fields are floats. We are writing some scripts that need to compare numbers and some of them look like the second row
Is there a way to get SQL Server 2005 to store NaN and Positive and Negative infinity values for double precision floats? It used to work fine under SQL 2000 and works great under MS Access, but we are updating our scientific application, and really need to be able to store this information efficiently under SQL Server 2005. We really want to dump Access because of database storage limitations.
It really amazes me that there is not more support for this as the limitation really makes it hard to store a lot of our simulation data. I guess SQL Server is more geared for financial data then scientific data. I know that Oracle supports this but we rather stick with SQL Server if we can.
We really want to avoid having to have an extra column to store this, that would be disastrous. NaN could be stored as null values. And, no, we cannot map +/- Infinity to Min and Max double values. We definitely need the distinction there.
Another post I read talked about custom user types€¦ but I€™m not sure how well that will work with our analysts pulling the data into other tools, like Excel.
Any help or suggestions would be appreciated. It just boggles the mind that SQL Server does not seem to handle true IEEE 754 doubles. How is Microsoft going to deal with C# 3.0 and DLinq support? The issue must show up there.
Hi, I have a database field of type "money". But when I rerieve it to text box, it shows 5.0000, but I want only 5.00 to be shown. How do I format that? Any reply will be much appreciated. :)
Hi! I'm quite new to SQL Server. I need to set a float datatype to display something like 3.55. However, all values that are stored in the float column are truncated to 4 or some other single digit. How can this be prevented?
I am sure this is a newbie question as I am new to Microsoft SQL server but any help is greatly appreciated. I am populating a SQL database from an AS400. The decimal numbers from the AS400 are coming accross with extra decimals. (ie. 63.02 is coming accross as 63.0200001)
Is there a way to limit the number of decimals in a float or real field - or a SQL command I can put in a script to truncate each field to 2 decimal places after they are populated.
I have been trying to develop an automatic way of programmatically accessing datasources and performing some predefined(-supported) processing on them.
The question I would like to ask you people has to do with numeric fields. What exactly is precision? Is it the maximum length in digits of a field, or is there more to it? What about a "field's scale", what is it and how does it affect a field's value handling?
I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
I am using ASP and SQL 2005 Express.I am inserting a timestamp from an ASP page using <%=now%into asmalldatetime field. All of my timestamps are appearing without anyseconds (e.g., 1/21/2008 4:02:00 PM or 1/18/2008 11:32:00 AM).When I view the source for my page is shows the date/time as 1/21/20084:27:31 PM, but for some reason the seconds will be converted to1/21/2008 4:27:00 PMHow do i get more a more precise timestamp?Please help.
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
The only solution I found is use DataReader Source,
But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).
My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....
Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???
Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.
We have a field which is decimal (9,2) and another which is decimal(9,3). Is there anyway to subtract the two and get a precision 3value without changing the first field to 9,3?For instance, retail value is 9,2, but our costs are at 9,3 due tobeing averaged. To calculate margin (retail-cost), we want that alsoto be 9,3, but a basic subtraction comes out 9,2. You can see wedon't want to increase retail to be 9,3 (that would look funny), andit seems wasteful to store retail twice (one 9,2 for users and one 9,3for margin calc)...is there any other way?
there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql?
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.
I am using SQL 2000 in a kind of electronic wallet way. Users out money onto an account and spend it on various services on a system. The cost of those services is deducted from the value in their wallet, and everybody's happy. However, some very strange things have been happening to my transactions; seemingly at random.
Some transactions (such as purchasing time on the Internet) are returning values such as 0.10000000000000001 instead of 0.1. This minute difference affects the user's wallet balance because the rouge digit is subtracted from their account. So instead of a balance of, say, 3.4 they have 3.39999999999999999.
"So what?", I hear you say. Well the problem comes when it's time to give them a refund. They walk over to a kiosk and the machine tells them they have 3.40 remaining in their account (it's nicely rounding up the value), but when they click Refund, it tells them they have insufficient funds to complete the refund! (Note: The refund amount is being compared with the wallet balance). If I go into the database via Query Analyzer it tells me their balance is 3.3999999etc, but in Enterprise Manager the value is 3.4. If try to manipulate the data in any way it is treated as 3.4. However, if I add 0.000000000000001 then QA reads the value as 3.4 and the customer can get their refund.
My questions is this. One, how the hell do I stop this from happening? I only need the two decimal places. Taking the value in a query and round it up/chopping off the remaining decimal points hasn't worked. It always picks up the value as 3.4 in a query. Two, why on Earth is this happening??? Has anyone experienced this problem before.
Thanks in advance to anyone that's read this far down.
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error: "The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38". the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
i get... today end_of_today ----------------------- ----------------------- 2008-06-04 00:00:00.000 2008-06-04 23:59:59.997
*/ ------------------------------------------------------------------------ --even as simple as this, the result is the same... select cast('23:59:59.999' as datetime) as end_of_day
I'm running a transformation script that's taking decimal(18,10) data and trying to shoehorn it into a numeric(9,6). generally this works, as most of the data in the original table is not using anywhere near the precision it's capable of, but once in a while I run into one that does use it.
Is there any way to automagically reduce the precision so that i can cram the data into the destination table?
This one cost me a solid half hour yesterday. I'm wondering why onearth the default precision for a decimal type is 18,0. Maybe I'mmistaken. A decimal datatype sort of implies that you'd want somethingafter the decimal!Question is, can I set this database-wide? Like all new decimaldatatypes have a precision of 12,6 or something like that? I haven'tseen anything about this in the googling I have done...
The fields fltValorPendente e fltTotal are of this type. Field intSinal is an Integer.
I execute the following query: SELECT (intSinal * fltValorPendente) / fltTotal as Coef, cast((intSinal * fltValorPendente) as decimal(21,6)) / fltTotal as CoefCast into tmp FROM tbl Where fltTotal<>0
As a result, table tmp is created with the following structure: CREATE TABLE [dbo].[tmp]( [Coef] [decimal](38, 6) NULL, [CoefCast] [decimal](38, 17) NULL ) ON [PRIMARY]
How come both fields don't get the same precision?
We have a view in a 9205 oracle database. We can query fine and the decimal precision is there. When we query this same view from ms sql server we lose the precision so 115.25 becomes 115. does anyone know a workaround for this?
Right now the database I am working with is storing time inan Integer data type and is storing the time value in seconds.The application does not allow entering seconds. It acceptsminutes and hours.I have a report where it is doing:SELECT SUM(TIMEENTERED)and the SUM is *blowing* up as the SUM is reachingthe BIGINT range.I can fix the problem by changing all codes to:SELECT SUM(CAST(TIMEENTERED AS BIGINT))But now that I ran into this problem I want to find outif storing the time in seconds using INTEGER datatype is the best solution?I've been searching this newsgroup and other placesthe whole day. I even ran into my own three year oldpost. Three years ago my problem was data migrationrelated and now it is more of performance related thananything else.http://groups.google.com/groups?as_...y=2006&safe=offI could not find this specific topic in SQL books likeSQL for Smarties 2005 by Joe Celko (very good stuff ontemporal topics but nothing specific to my question),or Inside SQL Server 2000.Which data type would be ideal and why?smalldatetime?integer?decimal?float?The type of operations that are being done in the databaseare:1- Entering time in hours on work done on a taskFor the data entry part, the application accepts2.5 as 2 and a half hours and it is storing2.5 * 3600 = 9000 seconds.It also accepts entering 2:30 as 2 hours and30 minutes and again storing 9000 seconds.I even saw a page where you can enter clocktime: I worked from 9:30AM to 12:45PMas an exampleWhen i checked the underlying table(s) I sawthat the ENTEREDTIME is always the durationin seconds. So the data entry can either be2.5 hours where ENTEREDTIME = 9000 secondsor9:00AM to 11:30AMwhere STARTDATE is today's date for examplestored as 1/27/2005 09:00AMand where ENTEREDTIME = 9000 seconds2- All kinds of reports showing total time in hoursfor example: Project1 = 18.5 hoursThe code in the SP are all like:SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIME3- I am sure a lot of other arithmetic calculations arebeing done with this ENTEREDTIME field.What would be the best way to store hours/minutesbased on how we are using Time in the database?Either I will stick with Integer but store in minutestime instead of calculating in seconds and most likelyupdate all the SUM(ENTEREDTIME) toSUM(CAST(ENTEREDTIME AS BIGINT))or I will switch to storing in decimal/float andmaybe avoid doing :SUM(ENTEREDTIME) / CAST(3600 AS DECIMAL(6,2))AS TOTALTIMEsince the ENTEREDTIME would already be storedin hours time.or I will use DATETIME since in the cases ofI worked from 9:00AM to 11:30AMI have to have a separate column to store the date also.I am a little confused I am hoping I will get some helpfrom you and maybe if I can't find the best solution, atleast eliminate the NOT so good ones I am thinking of.Thank you
I'm connecting MSSQL Server 2005 Express via MS Driver for PHP (CTP version October 2007) and sometimes I don't retrieve exact float values. For example, in database is 0.7 and I get 0.69999999999999996, but for 1.0 in database I get 1.0. The result is the same, if I use prepared statement (sqlsrv_conn_prepare() and sqlsrv_stmt_execute()) or directly sql_conn_execute().
The table definition is very simple: CREATE TABLE [dbo].[test]( a float NOT NULL ); insert into test values(0.7); insert into test values(1.0); insert into test values(1.1); insert into test values(1.2); insert into test values(1.3); insert into test values(1.4); insert into test values(1.5); insert into test values(1.6); insert into test values(1.7); insert into test values(1.8); insert into test values(1.9); insert into test values(2.0);
I´m having some trouble converting values represented as strings to the decimal data type. I have a flat file source, from where I read some currency rates represented without decimals. Before sending those values to my SQL Server destination, I want to convert them to represent correct values.
An example to clarify:
If my source contains a column named "curr_rate" with the value 000092500 I want to send it to my destination as 9,2500.
So I set up a Dervied column component, converting my value like so:
((DT_NUMERIC,9,4)curr_rate)/10000
My problems is that the precision is lost, and all that´s sent to my destination table is 9,0000.
How should I go about to convert my strings without losing precision in the process?
I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1
-- using results from other sources (c#, windows calc, casio.com) SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004 SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004 SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.
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.
I am using SQL CLR Integration to create a series of stored procedures.
I am building and deploying from Visual Studio 2005 SP1 and everything is working well except for my stored procedures that have a SqlDecimal typed input argument. By default, the precision and scale of the SqlDecimal is deployed to SqlServer as (18,0).
How can I change this default?
This is an example of my stored procedure definition:
