Values show up as rounded in SQL Server Management Studio but not when extracted

Question

I have set up the query underneath in Microsoft SQL Server Mgmt studio and am receiving interesting results in regards to the Rate and Variable rate columns.

When I run my query in Management Studio I receive the following result set: The columns are in the following order: 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

When I run my query outside of Management Studio in a batch file I receive the following result set:

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

How come SQL Server Management Studio is rounding the value but my batch file is not? I need to see the rounded value in my batch extract. I have tried changing the data types of the columns to decimal and real only to receive errors.

Does anyone have any suggestions as to how I can make this work and show the rounded values in my extract?

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

Your table definition would look like this:

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
);

And your data insert would look like this (assuming that the original values are floats or something similar, so you'd need to CAST or 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;

I am not sure what will happen when you try to CAST the Rate and VariableRate columns like this, because that depends on the data types they have in the RPT_TradesIssuance and InterestRateIndexes tables. Are they FLOAT as well?

(Please note, I chose NUMERIC(18, 2) and NUMERIC(18, 4) as data types, but you should adapt those according to the data that will be stored in those columns.)