sql - 值在 SQL Server Management Studio 中显示为四舍五入但在提取时不是

Question

我已经在 Microsoft SQL Server Mgmt studio 中设置了下面的查询，并且收到了有关 Rate 和 Variable rate 列的有趣结果。

当我在 Management Studio 中运行查询时，我收到以下结果集:列的顺序如下:TradeID,Dealer,IssuanceDate,MaturityDate,Face,Rate,Proceeds,TxnCCY,VariableRate

 Trade ID                Dealer   IssuanceDate MaturityDate Face     Rate    Proceeds  TXN CCY Variable Rate
 PERNOD & RICARD            BAR   20121212    20121221  10000000    0.24    9999400 USD NULL
PUT_30 04821QAP6 1ML POOL   BAS   20121022    20130418  100000000   0.28    100000000   USD 0.2075

当我在 Management Studio 之外的批处理文件中运行我的查询时，我收到以下结果集:

Trade ID                Dealer   IssuanceDate MaturityDate Face     Rate    Proceeds  TXN CCY    Variable Rate
PERNOD & RICARD;          BAR;20121212;        20121221;  10000000;0.23999999999999999;9999400;USD;
PUT_30 04821QAP6 1ML POOL;BAS;20121022;        20130418;  100000000;0.28000000000000003;100000000;USD;
       0.20749999999999999

为什么 SQL Server Management Studio 会四舍五入该值，而我的批处理文件却没有？我需要在我的批提取中查看舍入值。我已尝试将列的数据类型更改为十进制和实数，但仅收到错误。

对于我如何完成这项工作并在我的摘录中显示四舍五入的值，有没有人有任何建议？

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[CALYON_TRADES_LIABILITIES_TEST]
-- Add the parameters for the stored procedure here
@BusDate datetime = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
 IF @BusDate is null
  BEGIN
DECLARE @yesterday datetime
set @yesterday = DATEADD(D, -1, GETDATE())
set @BusDate = CONVERT(datetime,
convert(varchar(2), datepart(month, @yesterday)) + '/' + convert(varchar(2),datepart
(day, @yesterday)) + '/' + convert(varchar(4), datepart(year, @yesterday))
)
END
Drop table dbo.VariableRateLiabilities
Create table dbo.VariableRateLiabilities
(TradeID VarChar(35) null,
Dealer VarChar(15) null,
IssuanceDate varchar (8) null,
MaturityDate varchar (8) null,
Face numeric (9) null,
Rate float null,
Proceeds numeric null,
TxnCCY VarChar (3) null,
VariableRate float null,
VariableRateDate varchar (8) null)
INSERT INTO dbo.VariableRateLiabilities SELECT DISTINCT
RPT.TradeName as TradeID,
RPT.DealerShortName as Dealer,
CONVERT(varchar(8), RPT.TxnValueDate, 112) as IssuanceDate,
CONVERT(varchar(8), RPT.MaturityDate, 112) as MaturityDate,
RPT.Face,
RPT.Rate,
RPT.Proceeds,
RPT.TxnCCY,
IRI.InterestIdxRate as VariableRate,
CONVERT (varchar (8), IRI.InterestIdxDate, 112) as VariableRateDate
From RPT_TradesIssuance RPT
INNER JOIN LiabilityTrades LT
ON RPT.Price = LT.Price
LEFT OUTER JOIN InterestRateIndexes IRI
ON LT.InterestRateCode = IRI.InterestRateCode
WHERE RPT.SPVId=12
AND RPT.MaturityDate > @BusDate
AND RPT.TxnValueDate <= @BusDate
select TradeId,Dealer,IssuanceDate,MaturityDate,Face,Rate,Proceeds,TxnCCY,VariableRate
from dbo.VariableRateLiabilities
where TradeId NOT LIKE 'PUT%'
UNION
select         a.TradeId,a.Dealer,a.IssuanceDate,a.MaturityDate,a.Face,a.Rate,a.Proceeds,a.TxnCCY,a.VariableRate
from dbo.VariableRateLiabilities a
where a.VariableRateDate in (select MAX(b.VariableRateDate) from      dbo.VariableRateLiabilities b where a.TradeID = b.TradeID)

ORDER BY Dealer,1
END

Answer 1

您的表定义如下所示:

CREATE TABLE dbo.VariableRateLiabilities (
    TradeID VARCHAR(35) NULL
    ,Dealer VARCHAR(15) NULL
    ,IssuanceDate VARCHAR(8) NULL
    ,MaturityDate VARCHAR(8) NULL
    ,Face NUMERIC(9) NULL
    ,Rate NUMERIC(18, 2) NULL
    ,Proceeds NUMERIC NULL
    ,TxnCCY VARCHAR(3) NULL
    ,VariableRate NUMERIC(18, 4) NULL
    ,VariableRateDate VARCHAR(8) NULL
);

你的数据插入看起来像这样(假设原始值是 float 或类似的东西，所以你需要 CAST 或 CONVERT):

INSERT INTO dbo.VariableRateLiabilities
SELECT  DISTINCT
        RPT.TradeName AS TradeID
        ,RPT.DealerShortName AS Dealer
        ,CONVERT(VARCHAR(8), RPT.TxnValueDate, 112) AS IssuanceDate
        ,CONVERT(VARCHAR(8), RPT.MaturityDate, 112) AS MaturityDate
        ,RPT.Face
        ,CAST(RPT.Rate AS NUMERIC(18, 2)) AS Rate
        ,RPT.Proceeds
        ,RPT.TxnCCY
        ,CAST(IRI.InterestIdxRate AS NUMERIC(18,4)) AS VariableRate
        ,CONVERT (VARCHAR(8), IRI.InterestIdxDate, 112) AS VariableRateDate
FROM    RPT_TradesIssuance RPT
INNER JOIN LiabilityTrades LT ON RPT.Price = LT.Price
LEFT OUTER JOIN InterestRateIndexes IRI ON LT.InterestRateCode = IRI.InterestRateCode
WHERE   RPT.SPVId = 12
        AND RPT.MaturityDate > @BusDate
        AND RPT.TxnValueDate <= @BusDate;

我不确定当您尝试像这样CAST Rate 和VariableRate 列时会发生什么，因为这取决于数据RPT_TradesIssuance 和 InterestRateIndexes 表中的类型。它们也是 FLOAT 吗？

(请注意，我选择了 NUMERIC(18, 2) 和 NUMERIC(18, 4) 作为 data types ，但您应该根据数据调整它们将存储在这些列中。)