Working With Decimals Or Real Numbers

Aug 17, 2006

I have a number 8.30 how can I break apart the 2 parts of this

for more simplicity I need to take out 8 and 30 in 2 different variables, how can I do it



Please advice

View 13 Replies


ADVERTISEMENT

What Functions To Manipulate Numbers And Decimals

Oct 15, 2006

what re the sql functions to manipulate numbers, decimals, dates..
1/ like if I have 123443.78654 I want to display just 2 decimals : 123443.78 or 3 decimals ..
2/ also if I want to have bankers rounding
3/ how about dates conversion : from string to date type and vice versa and adding dates, substracting dates, getting the day, the month....
what re the SQL functions to do that pls

View 4 Replies View Related

How To Average Real Numbers ?

Oct 16, 2007



Hello, I have a very strange problem.

I have DB with type real numbers, I want to calculate the average using a select query with average function.
I get a fault answer.

Here is an example :

--- CODE START HERE ---


create table #table (data real)

insert #table values(2.994589)

insert #table values(2.994861)

insert #table values(2.994921)


-- You can see the real values show all the 6 digits after the point
select * from #table

-- This is the correct average
select sum(2.994589+2.994861+2.994921)/3

-- This is a fault answer

select avg(data) as average from #table

--- CODE END HERE ---


What am I doing wrong ?
I want to understand the mistake the SQL is doing and replicate it outside the SQL, do you know what going on in the way the SQL calculates the average on type real ?

BTW, if the the table definitions are : create table #table (data float) , the sql gives the right number...

Thanks,
Rann.

View 1 Replies View Related

Working With Decimals In User Defined Funtions

Jul 25, 2005

I am trying to get a decimal value from this simple user defined function, but it is not working.  CREATE FUNCTION dbo.GETANSWERRETURNS decimal(6,2)ASBEGIN        DECLARE @Result decimal(6,2)
    SELECT @Result = 10 / 4
    RETURN @Result
ENDThe real answer is 2.5, but I keep getting 2.  Also, if I replace 10 / 4 with 3 / 4,  I get the answer 0.  I tried several things like changing decimal to numeric and still get the same results.

View 3 Replies View Related

Working With Large Numbers

Nov 23, 2005

I need to compute factorials, but I hit a limit around 170!. Is thereanyway to handle numbers larger than the float data type can handle?Thanks.

View 3 Replies View Related

Sql SORT Order Not Working On Numbers?

Jul 20, 2005

Hello all... I'm using asp to get records from an access database, verysimilar to the way datagrid would work. The title of each column in my tableis a link that alternates the sort order between ascending and descending...my problem is that text WILL change its sort order just fine but nubers arenot always in order. ie: if sort order is ASC (ascending) I might see 2000,234, 789 (should be ordered but its not). I'm guessing that ASP is handingthe string as a text string (?) and getting confused, is there a way toforce ASP into treating the string as numerals if this is the case? anyother ideas? Thanks so much.here is one of my sql commands in case you want to see it. "sort" is avariable containing the recordset to sort by depending on which link isclicked. I hope I didn't confuse the whole issue because of a lack ofcaffiene over here :)strsql = "SELECT * FROM comments ORDER BY " & sort & " DESC"Thanks of the help, much appreciated.Eno

View 2 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Bcp And Decimals

Oct 25, 2000

I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???

View 2 Replies View Related

Help With Decimals

Feb 22, 2004

Hi all,
This is what I have:
DECLARE @OnTime int
DECLARE @UnControlled int
DECLARE @Volume int
DECLARE @GrossEffect decimal(10,2)
DECLARE @NetEffect decimal(10,2)
DECLARE @WeekEndDate datetime

SET @OnTime = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
SET @UnControlled = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
SET @Volume = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ARSScanType = 'D' AND ARSType='AN')
SET @GrossEffect = (@OnTime/@Volume * 100)
SET @NetEffect = ((@OnTime + @UnControlled)/@Volume * 100)
SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblEDITempARS)

INSERT INTO tblSummaryData2(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
VALUES ('AN',
@Volume,
@NetEffect,
@GrossEffect,
@WeekEndDate)

My numbers are:
OnTime=8089
Uncontrolled=6
Volume=8095

The GrossEffect comes as 0 and the NetEffect=100
AN809501002/21/2004

Why is my GrossEffect = 0??? it should be 99.9

View 1 Replies View Related

Help With Decimals

Feb 19, 2008

Hello, I have a table with a field with the format of 8 decimals. When I make a select to that table instead is given me (for example) 25,00 it gaves me 25,00000000. How can I fix the with the correct format? Thanks in advance.

View 2 Replies View Related

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View 1 Replies View Related

Decimals In SQL Server

Jul 7, 2005

I need to display decimal results from SQL Server queries, but the decimals keep getting truncated. For example, 9 divided by 4, keeps getting truncated to 2. Does anyone know how to display the whole result, including the decimals?

View 2 Replies View Related

Trimming Decimals

Aug 17, 2005

I have a field in my SQL table that is defined Decimal(10,8), which
provides me with the ability to have up to 8 digits after the decimal
place, however, if I store a value such as 3.14, it is stored as
3.14000000. This is fine in the database, but it is the same when it's
returned. I have tried using Convert.ToDecimal on my returned row but
it doesn't work. The value at runtime appears as 3.14D in the locals
window but displays with all the insignifcant digits.

Any ideas what's happening or if there is an easy way to trim the trailing zeros?

View 2 Replies View Related

Decimals In View

Aug 24, 2004

Hello,

I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))

181.25
176.5
170

I want the view to show
181.25
176.50
170.00

I have tried Cast but that doesn't seem to do the job.

Help!

Rolf

View 1 Replies View Related

No Decimals In Query

Jun 24, 2008

in quantity column - i have

100.00000

but i want only 100

no decimals..

how to get that?


thanks

View 2 Replies View Related

DECIMALS IN QUERY

May 31, 2007

I have two fields whose format in the table is decimal (14,5).
I now have to divide one field by the other, and I want the result
to be in the format decimal (21,16).
Whatever I do I'm not able to get this result with sixteen decimals.
I tried with dec(Field1,21,16)/Field2 but it yields six decimals.
I tried with dec(Field1,21,16)/Field2 but it yields an integer.
I tried other ways but I always get a result which is made of maximum
six decimals.
What can I do?
Thank you.
Anna - Verona (Italy)

View 5 Replies View Related

Truncated Decimals

Jan 16, 2006

I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?

View 6 Replies View Related

Decimals With Sum Agregate Function

Feb 23, 2001

I'm using the sum() agregate function inside a select with a decimal field with an scale of 2.
I want to get the result always with 2 decimals, but if the result is 100.10 I get 100.1 . I've tried
to convert and cast the result but I can't find how to get 2 decimals. Please help. Thanks

View 1 Replies View Related

Updateing DECIMALs With DB-Library

Aug 18, 2004

Hello,

I have encountered a problem doing an update on a Field defined as DECIMAL with dbcursor().

I have opened a cursor with dbcursoropen() with option LOCK_CC set. Then I have bound some char variables to CHAR Fields with STRINGBIND and some double Variables to the DECIMAL fields with FLT8BIND.

When I read the cursor, the data is correct in the bound variables. But when I do an update, all data for the numeric fields are gone and the database contains only NULL Values. The Strings are updated correctly.

I have tried to set the length, I am using the poutlen-variables with a value other than 0, but nothing helps.

Does anyone have an idea about what can I do?

Thank You in advance.

Frank

View 3 Replies View Related

How To Add Trailing Zeros 2 Decimals

May 18, 2008

In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.

Thanks for the info.

View 1 Replies View Related

How To Add Trailing Zeros If There Is No Decimals

May 19, 2008

How can i add two trailing decimals if there is no decimals:

for example if it is just 1, then make it 1.00

if it is 1.12 then leave it as it is.

can you please help.

select cast(ordernumber as varchar(10)) + '00' from ordertable

Thanks for the info.

View 1 Replies View Related

Doubt In Truncating Decimals..

May 22, 2008

Hello

I need a help ..
I need to convert 45.4593251 to 45.46

How to achieve it.
Can any one help me please..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 3 Replies View Related

Problem Saving Decimals

Nov 10, 2005

I want to save a decimal in my sql table. It's an hourly rate. I have my field as smallmoney type. I am updating the table using a sp from my web page. It always rounds my entry up eg 3.75 becomes 4.00. Heres my sp and the line where I am passing the parameter to the sp.

CREATE PROCEDURE spRB_AmendRoomData

@strRoomId int,
@strRoomRef nvarchar (50),
@strFloor nvarchar (50),
@strZone nvarchar(10),
@strLocation nvarchar (100),
@strType nvarchar(50),
@strNo int,
@strDirections nvarchar(2000),
@strPhoneNo nvarchar (50),
@strMaxNo int,
@strEquipmentFac nvarchar(2000),
@strNotes nvarchar(2000),
@strCharge smallmoney,
@strNotInUse bit,
@strIntExt nvarchar(20)


AS
BEGIN
UPDATE tblRB_Rooms SET

RM_RoomRef=@strRoomRef,
RM_Floor=@strFloor,
RM_Zone=@strZone,
RM_Location=@strLocation,
RM_Type=@strType,
RM_No=@strNo,
RM_Directions=@strDirections,
RM_PhoneNo=@strPhoneNo,
RM_MaxNo=@strMaxNo,
RM_Equipment_Facilities=@strEquipmentFac,
RM_Notes=@strNotes,
RM_Charge=@strCharge,
RM_NotInUse=@strNotInUse,
RM_IntExt=@strIntExt


WHERE
RM_RoomId = @strRoomid

END
GO


Cmd.Parameters.Add(New SqlParameter("@strCharge", Me.txtCharge.Text))

Thanks for any help

View 4 Replies View Related

Find Number Of Decimals

May 14, 2008

Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution.

(a bug forgot to round)

Thanks for your help

View 4 Replies View Related

Need An Expression Format To Put Decimals .00

Mar 27, 2007

I have the following field(Fields!SequenceNO.Value), coming form database.

i would like to have an expression IIF...



if the value coming from database is just 1, then make it 1.00 or

if the value coming from database has a period or point say 1.01 then show as it is.



thank you very much for the help / information....

View 1 Replies View Related

Transact SQL :: Two Decimals Multiply Each Other Become Zero

May 18, 2015

Script as follows :

select cast( 0.0050000000 as decimal(38,23)) * cast(0.0000010000 as decimal(38,23))
select cast( 0.0050000000 as decimal(28,23)) * cast(0.0000010000 as decimal(28,23))
select cast( 0.01 as decimal(28,15)) * cast(0.0000000001 as decimal(28,15))
select cast( 0.01 as decimal(28,16)) * cast(0.0000000001 as decimal(28,16))

The result was following:

0.0000000--was zero
0.000000005000000000000000000
0.00000000000--was zero
0.0000000000010

View 3 Replies View Related

Problem Rounding Decimals

Apr 15, 2008

Hi,
Im having a decimal value of 1.24
im rounding off the value to 1 precision
ex:- select round(1.24,1)
the output its returning is 1.20
i want to get only 1.2
can anyone suggest on this??

View 3 Replies View Related

Decimals Converting Back To Integers? What's Going On?

Feb 23, 2007

I am trying use the decimal data type for a field in SQL Server.  When I input the values below, they round off.
73.827 Rounds to 74
1925.1 Rounds to 1925
119.79 Rounds to 120
What am I missing?  Access never gave me this issue.  Do you see any reason this would happen? I am entering the values into the table directly!

View 5 Replies View Related

Handling Decimals In Creation Of Table

Sep 29, 2014

I have a table I am trying to create and one of the columns needs to have a decimal that appears as 00.00 and does not round because the data example is 87.09, 86.50, 98.55 etc. I have tried every type of decimal formatting when creating I can find and nothing has worked. When I create the table and upload the data it keeps rounding like this: 87.00, 87.00, 99.00

The only way it does not round is if I do it as a char but that does not seem right when dealing with numerics.

View 1 Replies View Related

How Can I Show Decimals After Division Update

Jul 20, 2005

I have a table which I want to update by dividing one field intoanother. The update runs with no errors, but the results come out asonly a positive integer number.The datatype for the result field is float.The datatype for the other fields are int.I have tried testing by dividing 7 by 10000, which should give me0.0007. The result in the database is 0.How do I define a field to show the full value with decimal positions?If I enter .0007 into the field through the Enterprise Manager, it shows0007. When I update by dividing, it shows 0.Any help would be welcome.Joel*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved