Geek Answers Handbook

SQL decimal values ​​inserted as ints

I run a query that returns decimals and then inserts these decimals into a table variable. When I query a table variable, I get integers. Can anyone understand why this is happening and how to fix it? Using Sql Server 2000.

DECLARE @Nov Table(custCode varchar(10), PromiseAvg decimal, ShipAvg decimal ) INSERT INTO @Nov SELECT JM.CustomerCode , isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(PromiseDate, 0)) As Decimal)),0) As PromiseAvg , isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(BOL.ShipDate, 0)) As Decimal)),0) As ShipAvg from jobitems JI LEFT JOIN jobmaster JM ON JI.Job_Number = JM.JobNumber LEFT JOIN dbo.NCL_BOLDetails BD ON JM.JobNumber = BD.JobNumber AND JI.Item_Code = BD.ItemNumber INNER JOIN dbo.NCL_BOLs BOL ON BD.BOLID = BOL.BOLID WHERE StartDate BETWEEN '20091101' AND '20091130' Group By JM.CustomerCode, JM.CustLongName Order By JM.CustomerCode Select * from @Nov
+4
source share
1 answer

You need to determine the scale and accuracy for the decimal places in the table variable.

eg.

 DECLARE @Nov TABLE( custCode VARCHAR(10), PromiseAvg decimal(18,8), ShipAvg decimal(18,8) )

or any scale and accuracy is right for you. I just tested it on SQL Server 2008 and looks like decimal(18,0) by default, unless specified in the create table statement.

Also do the same with your cast s

 ... isnull(AVG(Cast(DATEDIFF(dd, ISNULL(startDate, 0), ISNULL(PromiseDate, 0)) As decimal(18,8))),0) As PromiseAvg, ....
+6
source

More articles:


All Articles