|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to store decimals in tables
I have a table with a field called weight, which i have it as a
decimal(18,2) data type. When i tried to store a value like 9.97, the actual data stored in the table is rounded off to 10. How do i fix this? What data type should i use? Thanks. How do you insert the data? How do you know how the data is actually stored?
Are you using DBCC PAGE or just looking at what is returned in some query? How are you retrieving and displaying it? What version and what tools are you using? Show quoteHide quote "fiaola" <fia***@mail.com> wrote in message news:%23Nx476PVJHA.4680@TK2MSFTNGP06.phx.gbl... >I have a table with a field called weight, which i have it as a >decimal(18,2) data type. When i tried to store a value like 9.97, the >actual data stored in the table is rounded off to 10. How do i fix this? >What data type should i use? > > Thanks. > Kalen,
I'm using a Stored Procedure. I'm using SQL Manager to view the table. No, im not using DBCC PAGE. I'm using Delphi to write the interface using ADO to connect. The data types in Delphi is FLOAT. I also tried using DOUBLE as data type from Delphi. Thanks for your help. Show quoteHide quote "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:Oy6Pi$PVJHA.5160@TK2MSFTNGP04.phx.gbl... > How do you insert the data? How do you know how the data is actually > stored? Are you using DBCC PAGE or just looking at what is returned in > some query? How are you retrieving and displaying it? > What version and what tools are you using? > > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > www.SQLTuners.com > > > "fiaola" <fia***@mail.com> wrote in message > news:%23Nx476PVJHA.4680@TK2MSFTNGP06.phx.gbl... >>I have a table with a field called weight, which i have it as a >>decimal(18,2) data type. When i tried to store a value like 9.97, the >>actual data stored in the table is rounded off to 10. How do i fix this? >>What data type should i use? >> >> Thanks. >> > > Decimal(18,2) should work for that. What is the output if you run the
following query? declare @decimaltest table (weight decimal(18,2)) insert into @decimaltest (weight) values (9.97) select * from @decimaltest Show quoteHide quote "fiaola" <fia***@mail.com> wrote in message news:%23Nx476PVJHA.4680@TK2MSFTNGP06.phx.gbl... >I have a table with a field called weight, which i have it as a >decimal(18,2) data type. When i tried to store a value like 9.97, the >actual data stored in the table is rounded off to 10. How do i fix this? >What data type should i use? > > Thanks. > here is my SP. The application is passing the params as (9.97), but when i
store it in the table, it rounds to 10. Thanks for your help. ALTER PROCEDURE [dbo].[usp_CREATE_FISH_CLEANING] @CleanDateTime datetime, @CleanTableID varchar(2), @CleanEmpID varchar(10), @CleanFishWeight Decimal(2) AS BEGIN SET NOCOUNT ON; IF @CleanEmpID <> '' BEGIN INSERT INTO FISHCLEANING ( Date, TableID, EmpID, Weight ) VALUES ( @CleanDateTime, @CleanTableID, @CleanEmpID, @CleanFishWeight ) END ELSE RETURN END Show quoteHide quote "Jonathan Kehayias" <jmkehayias.nospam@nospam.google.com> wrote in message news:O1YFp$PVJHA.4092@TK2MSFTNGP04.phx.gbl... > Decimal(18,2) should work for that. What is the output if you run the > following query? > > declare @decimaltest table > (weight decimal(18,2)) > insert into @decimaltest (weight) values (9.97) > select * from @decimaltest > > -- > Jonathan Kehayias > SQL Server MVP > http://jmkehayias.blogspot.com > http://www.sqlclr.net > > > "fiaola" <fia***@mail.com> wrote in message > news:%23Nx476PVJHA.4680@TK2MSFTNGP06.phx.gbl... >>I have a table with a field called weight, which i have it as a >>decimal(18,2) data type. When i tried to store a value like 9.97, the >>actual data stored in the table is rounded off to 10. How do i fix this? >>What data type should i use? >> >> Thanks. >> > > You have declared the CleanFishWeight parameter as DECIMAL(2) without
specifying scale. The default scale is 0 which results in rounding. To get the correct results change the parameter to appropriate precision and scale, like DECIMAL(6, 2). Again, how do you know how it is STORED inside the database? It seems like
all you know is what you see when you retrieve it. That does not indicate how it is actually stored. Your parameter datatype DECIMAL(2) doesn't mean two decimal digits, it means two total digits total with no digits after the decimal point, the same as DECIMAL (2,0) You should declare the parameter the same as the column in the table. Show quoteHide quote "fiaola" <fia***@mail.com> wrote in message news:%23YcmoLQVJHA.4456@TK2MSFTNGP06.phx.gbl... > here is my SP. The application is passing the params as (9.97), but when > i store it in the table, it rounds to 10. Thanks for your help. > > ALTER PROCEDURE [dbo].[usp_CREATE_FISH_CLEANING] > > @CleanDateTime datetime, > > @CleanTableID varchar(2), > > @CleanEmpID varchar(10), > > @CleanFishWeight Decimal(2) > > AS > > BEGIN > > SET NOCOUNT ON; > > IF @CleanEmpID <> '' > > BEGIN > > INSERT INTO FISHCLEANING ( Date, TableID, EmpID, Weight ) > > VALUES ( @CleanDateTime, @CleanTableID, @CleanEmpID, @CleanFishWeight ) > > END > > ELSE > > RETURN > > END > > > "Jonathan Kehayias" <jmkehayias.nospam@nospam.google.com> wrote in message > news:O1YFp$PVJHA.4092@TK2MSFTNGP04.phx.gbl... >> Decimal(18,2) should work for that. What is the output if you run the >> following query? >> >> declare @decimaltest table >> (weight decimal(18,2)) >> insert into @decimaltest (weight) values (9.97) >> select * from @decimaltest >> >> -- >> Jonathan Kehayias >> SQL Server MVP >> http://jmkehayias.blogspot.com >> http://www.sqlclr.net >> >> >> "fiaola" <fia***@mail.com> wrote in message >> news:%23Nx476PVJHA.4680@TK2MSFTNGP06.phx.gbl... >>>I have a table with a field called weight, which i have it as a >>>decimal(18,2) data type. When i tried to store a value like 9.97, the >>>actual data stored in the table is rounded off to 10. How do i fix this? >>>What data type should i use? >>> >>> Thanks. >>> >> >> > > Thanks Problem solved. My ADO component was not changed to match the data
type specified in DB. Thanks for all the help. Show quoteHide quote "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:Om96XjQVJHA.2928@TK2MSFTNGP03.phx.gbl... > Again, how do you know how it is STORED inside the database? It seems like > all you know is what you see when you retrieve it. That does not indicate > how it is actually stored. > > Your parameter datatype DECIMAL(2) doesn't mean two decimal digits, it > means two total digits total with no digits after the decimal point, the > same as DECIMAL (2,0) > > You should declare the parameter the same as the column in the table. > > -- > HTH > Kalen Delaney, SQL Server MVP > www.InsideSQLServer.com > www.SQLTuners.com > > > "fiaola" <fia***@mail.com> wrote in message > news:%23YcmoLQVJHA.4456@TK2MSFTNGP06.phx.gbl... >> here is my SP. The application is passing the params as (9.97), but when >> i store it in the table, it rounds to 10. Thanks for your help. >> >> ALTER PROCEDURE [dbo].[usp_CREATE_FISH_CLEANING] >> >> @CleanDateTime datetime, >> >> @CleanTableID varchar(2), >> >> @CleanEmpID varchar(10), >> >> @CleanFishWeight Decimal(2) >> >> AS >> >> BEGIN >> >> SET NOCOUNT ON; >> >> IF @CleanEmpID <> '' >> >> BEGIN >> >> INSERT INTO FISHCLEANING ( Date, TableID, EmpID, Weight ) >> >> VALUES ( @CleanDateTime, @CleanTableID, @CleanEmpID, @CleanFishWeight ) >> >> END >> >> ELSE >> >> RETURN >> >> END >> >> >> "Jonathan Kehayias" <jmkehayias.nospam@nospam.google.com> wrote in >> message news:O1YFp$PVJHA.4092@TK2MSFTNGP04.phx.gbl... >>> Decimal(18,2) should work for that. What is the output if you run the >>> following query? >>> >>> declare @decimaltest table >>> (weight decimal(18,2)) >>> insert into @decimaltest (weight) values (9.97) >>> select * from @decimaltest >>> >>> -- >>> Jonathan Kehayias >>> SQL Server MVP >>> http://jmkehayias.blogspot.com >>> http://www.sqlclr.net >>> >>> >>> "fiaola" <fia***@mail.com> wrote in message >>> news:%23Nx476PVJHA.4680@TK2MSFTNGP06.phx.gbl... >>>>I have a table with a field called weight, which i have it as a >>>>decimal(18,2) data type. When i tried to store a value like 9.97, the >>>>actual data stored in the table is rounded off to 10. How do i fix >>>>this? What data type should i use? >>>> >>>> Thanks. >>>> >>> >>> >> >> > >  
Other interesting topics
in full recovery mode but log keeps self-truncating
Server Disk Space Max server memory being ignored Consolidate datafiles to one drive Security Question Cumulative package 2 for SQL 2008 Maintenence Cleanup Task does not delete backup files Hebrew + Arabic collation How to do bulk update in SQL Server 2005. 32 vs 64 bit SQL Server hotfixes |
|||||||||||||||||||||||
