My Decimal Value Is Being Rounded Of When It Gets To The Db
Jan 28, 2004
Hi
I'm inserting a decimal value into my db but it keeps round it of to a single figure. Its the result of time between two times. ie between 1:00 and 2:30 equals 2.5. But it ends up as 2 in the db even though the db field is also decimal. I know by looking at the watch in the bugger that its actually a decimal value just prior to insertion which only leads me to suspect that sql server is doing the rounding of, maybe this is the default behaviour but i need it to store the values as decimal.
View 9 Replies
ADVERTISEMENT
Oct 14, 2005
I am writing a simple shopping cart app and the price field is being rounded when inserted into the database.There is a textbox for the user to enter the price. A stored procedure is used to insert the line item info to the data base table. The field in the table is formatted as datatype decimal. Below is the stored procedure and the code that adds the value to the parameter. Any suggestions on where I am going wrong?<Code>CREATE PROCEDURE [sp_insert_CartDetail] ( @ReferenceNum [int], @Item [varchar](26), @Desc1 [varchar](27), @Desc2 [varchar](20), @Desc3 [varchar](30), @Desc4 [varchar](30), @Note [text], @Quantity [int], @DateOrdered [datetime], @SalesPrice [decimal], @DistCost [decimal], @SalesTaxable [smallint], @RequiredDate [datetime], @User1 [varchar](12), @User2 [varchar](12), @User3 [varchar](12), @User4 [varchar](12), @User5 [varchar](12), @User6 [varchar](12))
AS INSERT INTO [ToolCrib].[dbo].[CartDetail] ( [ReferenceNum], [Item], [Desc1], [Desc2], [Desc3], [Desc4], [Note], [Quantity], [DateOrdered], [SalesPrice], [DistCost], [SalesTaxable], [RequiredDate], [User1], [User2], [User3], [User4], [User5], [User6]) VALUES ( @ReferenceNum, @Item, @Desc1, @Desc2, @Desc3, @Desc4, @Note, @Quantity, @DateOrdered, @SalesPrice, @DistCost, @SalesTaxable, @RequiredDate, @User1, @User2, @User3, @User4, @User5, @User6)SqlCmdInsetCartDetail.Parameters("@SalesPrice").Value = CType(txtCost.Text, Decimal)
</code>
View 5 Replies
View Related
Jun 19, 2007
Hello!
I´m having some trouble with a database field that I had to redefine. At first, it was defined as an int, but I had to change it to decimal (5,2).
I have an application which is accessing my database. Problem now is that when if I enter a decimal value, say 3,12 into my field and then press save on my BindingNavigator, this value is instantly rounded to 3,00. I tried entering the value directly into my database, and that´s no problem. The rounding seems to happen on the update statement. I tried stepping through the code, and after this particular line, the value is rounded... Any help?
Me.OrdersTableAdapter.Update(Me.OrdersDataSet.Orders)
Regards
Daniel
View 3 Replies
View Related
Feb 6, 2008
I have this case statement below it is for an access fee. My problem that I am having is that now I am getting a number that is huge. Example, 8.2350000000. I need this to be rounded up and numbers srinked to 8.24, how can I add that in my case statement below? Please help if you can.
Thanks
CASE clm_att1
WHEN 'NG' THEN CASE clm_h30 WHEN 0 THEN cast(clm_sppo as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
ELSE cast(clm_H30 as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
END
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN CASE clm_h30 WHEN 0 THEN cast(clm_sppo as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
ELSE cast(clm_H30 as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)*.65
END
ELSE Null
END AS AccessFeeFinal,
View 2 Replies
View Related
Apr 20, 2007
I am designing a report in which the edges of the tables should be curved. Is there any way to make the edge of the tables rounded? Could any one suggest for this. I am using MS Reporting service 2003
View 2 Replies
View Related
Sep 17, 2015
I came across the below scenario in SSIS where the values are not getting rounded off in SSIS as expected.
I am passing the value 23007.945 and using a derived column expression to round off the value Round(number,2).
Placed a data viewer to view the outcome after the derived column expression.Value after derived column expression seems to be 23007.94 but the same when passed in SQL select round(23007.945,2) gives us the value as 23007.95.
What could be causing this?
View 0 Replies
View Related
Jan 3, 2008
I have created the following user function :-
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBillPrice](@BillLineID int)
returns float
as
begin
DECLARE @BillPrice float
SET @BillPrice = (
SELECT ((CAST(T_BillValueAndTax.BillValueAndTaxBillValue as float))+(CAST(T_BillTax.BillTaxTax1Value as float)) +(CAST(T_BillTax.BillTaxTax2Value as float)))
FROM T_BillLine
INNER JOIN T_BillValueAndTax on T_BillValueAndTax.BillValueAndTaxID = T_BillLine.BillValueAndTaxID_Price
INNER JOIN T_BillTax on T_BillTax.BillTaxID = T_BillValueAndTax.BillTaxID_Bill
WHERE T_BillLine.BillLineID = @BillLineID)
return ABS(@BillPrice)
end
No matter how I try to return the @BillPrice value, the value is rounded to the nearest whole number. For instance if the value is calculated as 198.75 the value returned is 199.0
When I execute the function in Query Anlayser the correct value of 198.75 is returned.
Any help in solving this would be greatly appreciated.
View 10 Replies
View Related
Sep 17, 2015
I came across the below scenario in SSIS where the values are not getting rounded off in SSIS as expected.
I am passing the value 23007.945 and using a derived column expression to round off the value Round(number,2).
Placed a dataviewer to view the outcome after the derived column expression.Value after derived column expression seems to be 23007.94 but the same when passed in SQL select round(23007.945,2) gives us the value as 23007.95.
What could be causing this?
View 4 Replies
View Related
Mar 30, 2015
In My table formula(varchar) and value(Float) column.
Example
formula --> (A+B)/C
I am trying to create same expression with value But my below example showing value gets rounded if it is big.
I am trying to do,
Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456
any way to keep same value as It is?
View 5 Replies
View Related
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
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
Apr 29, 2008
Hello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
View 5 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
Sep 19, 2007
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl
View 4 Replies
View Related
Jul 23, 2005
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies
View Related
Nov 30, 2007
Hi all,
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Any help would be much appreciated
Thanks!
View 5 Replies
View Related
Jun 18, 2007
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
View 3 Replies
View Related
Jun 4, 2007
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
View 4 Replies
View Related
Jan 30, 2006
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
View 3 Replies
View Related
Jul 8, 2004
Is there a way to create a SP or UDF that converts a decimal number to hexidecimal notation?
View 3 Replies
View Related
Jul 23, 2005
i am having trouble the with the line below:SELECT AVG(CAST(RATE1 AS Decimal(9, 2))) AS Expr1i am trying to do an average on the column "rate1" and want only 2decimal points but the syntax i wrote doesn't do it. is theresomething i am missing or doing wrong?when i run the above syntax i get 7.246428 and i need it to be 7.2 iknow this is possible in SQL but for some reason cant figure it out.Any help would be greatly appreciated.thanks
View 2 Replies
View Related
Jul 11, 2006
Is there any disadvantages of using Decimal type as a primary key? Ifso, are there other data types that can hold large numbers (more thanan int can hold: 13 digits or so) and perform better as a Key field?Thanks.
View 3 Replies
View Related
Mar 19, 2008
I imported some feilds from a text file. Most of which i set to varchar. However, I want one of the feilds to have 2 decimal places.
I tried changing it in the table design after the import and nothing changed.
Then i tried re-writing the query for insertiing the values, where i changed varchar(11) to numeric (11,2) but i got an error. "Error converting data type nvarchar to numeric."
Any suggestions on resolving this glitch ??
View 8 Replies
View Related
Jul 17, 2006
I just created a new table in my database. One of the fields in that table has a data type of money. Would my C# code be better off referring to this field as SqlMoney or as a decimal and why?
Would I be better off altering the field in the table so that it has a data type of decimal?
Thanks
View 12 Replies
View Related
Dec 18, 2006
Hi guys,I've got a table with one of the columns 'ConversionRate' declared as decimal. The value for this column is defined as 1.45. In my vb file, I tried to retrieve the value using a SqlParameter as shown below:myConversionRate = Me.SqlCommand.Parameters.Add(Sql.StoredProcParameter.ConversionRate, SqlDbType.Decimal)myConversionRate.Direction = ParameterDirection.Output which will access the following query in my stored proc: ALTER PROCEDURE dbo.Charge
@PConversionRate DECIMAL = 0 OUTPUT
AS
SELECT @PConversionRate = ConversionRate,
FROM TblCharge
For some reasons, the value returned is always 1 instead of 1.45. Anyone experienced this problem before and knows how to resolve it? Thanks in advance.
View 7 Replies
View Related
Apr 17, 2007
I have a money field in SQL that when i try and get the sum of it i cannot convert it to decimal. This was working now its not, and nothing was changed.Any reason for the error? DECLARE @TEST decimal(10,2)SET @Test = (SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID='0000010604')Print @TestRETURNS: 160471.24----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Specified cast is not valid. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.InvalidCastException: Specified cast is not valid.Source
Error:
Line 3576: // CURRENT TOTALLine 3577: cmd.CommandText = "SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID=@LoanID";Line 3578: decimal split_currentamt = ((decimal)cmd.ExecuteScalar());
View 6 Replies
View Related
May 31, 2007
Hi,
I am trying to get my string to 2 decimal places...
Here is the code:string sumSQL = "SELECT CONVERT (float,sum(callTime)/count(callTime)) as CAVG FROM leads where agent = '" + agent + "' and " + dates.Text;
SqlCommand sumCMD = new SqlCommand(sumSQL, conn2);
object null1 = sumCMD.ExecuteScalar();if (null1 != null)
{string avgS = sumCMD.ExecuteScalar().ToString();Response.Write(avgS + "<br>" + "</td></tr><tr>");
}
Any Help appreciated...
Doug
View 1 Replies
View Related
Feb 6, 2008
Hi all - am in a bit of a quandry over this one!My application is all up and running - it's an despact & accounts app - I've missed one thing though - the ability to deal with half pennies (or half cents for those of you over the pond ;))
All of my tables are set up as decimal (19,2) along with all the params in my stored procs (a lot!). Is there an easy way to change all of these in 1 swoop?
Thanks in advance,
Stephen.
View 1 Replies
View Related
Apr 12, 2008
Hi i have a insert stored procedure, which i need to insert a label.text which is 12.99
the problem i have is that it is inserting as 13.00 any ideas what i need to do??Dim Price As Decimal = CostLabel.Text then i am inserting with this param....
command113.Parameters.Add("@Cost", SqlDbType.Decimal).Value = Price
View 4 Replies
View Related
Dec 23, 2003
Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.
For instance 12.5 will insert as 12.
Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@Num", SqlDbType.Decimal))
MyCommand.Parameters("@Num").Precision = 5
MyCommand.Parameters("@Num").Scale = 2
MyCommand.Parameters("@Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]
I also declared @Num to be a decimal in my Stored Procedure.
As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.
Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.
View 1 Replies
View Related
Mar 10, 2006
I want to format a number, a decimal, with 2 figures afther the "," but all I get is zeroes behind it. It works if I replace "cart.GetTotalWeight(cartId)" by 12.12 for example, 12,12 does not work... It driving me crazy
dim cart = New ShoppingCartDB()TotalweightLbl.Text = string.Format("{0:n}", cart.GetTotalWeight(cartId))
the stored procedure...
CREATE Procedure CMRC_ShoppingCartTotalweigh ( @CartID nvarchar (50), @Totalweight decimal (5,2) OUTPUT ) AS
SELECT @Totalweight = SUM(Products.Unitweight * ShoppingCart.Quantity) FROM ShoppingCart, Products
WHERE ShoppingCart.CartID = @CartID AND Products.ProductID = ShoppingCart.ProductIDGO
the function....
Public Function GetTotalweight (ByVal cartID As String) As Decimal
' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("CMRC_ShoppingCartTotalweigh", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50) parameterCartID.Value = cartID myCommand.Parameters.Add(parameterCartID)
Dim parameterTotalweight As SqlParameter = New SqlParameter("@Totalweight", SqlDbType.decimal, ) parameterTotalweight.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterTotalweight)
' Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close()
' Return the Total
If parameterTotalweight.Value.ToString() <> "" Then Return parameterTotalweight.Value Else Return 0 End If
End Function
View 1 Replies
View Related
Mar 3, 2001
I need to know a general syntax for inserting user information from an application into 6 Tables using one stored procedure. Is it something like:
Select fieldname, fieldname AS INSERT INTO tablename (@fieldname, @fieldname)
and keep going down the line, or do I need a BEGIN or GO somewhere?
Thanks!
View 2 Replies
View Related
Nov 1, 2002
I need to import an nvarchar that contains 1/4 (for example) to a float field so it would read .25 Any suggestions? Thanks
View 3 Replies
View Related
