Need Help in SQL Stored Procedure 0.00 to .00
I am trying to querying a database to export to a bar delimited file using stored procedure.
However I am getting .00 instead of 0.00 in my exported file. In SQL server showing 0.00 but it becomes .00 in the bar delimited file.
I have tried everything, playing with different datatypes in SQL server, different datatypes, formatting but nothing worked.
Below please find my stored procedure to export bar delimited file:
CREATE PROCEDURE [dbo].[GAF_Ledger]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
/*Create temporary table*/
CREATE TABLE ##LRec (
ID char(1) DEFAULT 'L',
RefDate date NULL,
Account nvarchar(15) NULL,
AcctName nvarchar(100) NULL,
Memo nvarchar(50) NULL,
CardName nvarchar(100) NULL,
TransID int NOT NULL,
BaseRef nvarchar(11) NULL,
TransType nvarchar(20) NULL,
SET NOCOUNT ON;
INSERT INTO ##LRec (ID,RefDate,Account,AcctName,Memo,CardName,TransID,BaseRef,TransType,Debit,Credit,Balance)
SELECT 'L', CONVERT(nvarchar(10),T0.[RefDate],103) as DocDate, T2.FormatCode as 'Account ID', T2. AcctName,T0.Memo,T3.CardName, T0.[TransId], T0.BaseRef,
WHEN T0.TransType = 13 THEN 'AR Invoice'
WHEN T0.TransType = 14 THEN 'AR Cred Memo'
WHEN T0.TransType = 18 THEN 'AP Invoice'
WHEN T0.TransType = 19 THEN 'AP Cred Memo'
WHEN T0.TransType = 24 THEN 'Incoming Payment'
WHEN T0.TransType = 30 THEN 'Journal Entry'
WHEN T0.TransType = 46 THEN 'Outgoing Payment'
END AS 'Trans Type', T1.Debit, T1.Credit, T1.Debit - T1.Credit As 'Balance' FROM OJDT T0 INNER JOIN JDT1 T1 ON T1.TransId = T0.TransId LEFT JOIN OCRD T3 ON T3.CardCode = T1.ShortName INNER JOIN OACT T2 ON T2.AcctCode = T1.Account WHERE T0.RefDate >=@StartDate and T0.[RefDate]<= @EndDate
SET NOCOUNT OFF
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##LRec" queryout C:\Data\L_Record.txt -t"|" -c -T'
I need to show 2 decimal places for Debit, Credit & Balance. However those 0.00 records are being exported as .00 in the bar delimited file.
If you are able to assist me with this I would be greatly appreciated.