Need Help in SQL Stored Procedure 0.00 to .00

[former_member1133820]

Dear all,

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]

@StartDate datetime,

@EndDate datetime

AS

BEGIN

  -- 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,

Debit numeric(19,2),

Credit numeric(19,2),

Balance numeric(19,2)

  ) 

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,

CASE

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'

ELSE 'Other'

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'

END

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.

Thanks.

Best Regards,

Leng

[Johan_H · Accepted solution]

Hi Leng,

Have you tested a different datatype for the numeric fields in your temporary table ?

Debit float,

Credit float,

Balance float

instead of

Debit numeric(19,2),

Credit numeric(19,2),

Balance numeric(19,2)

Regards,

Johan

[former_member1133820 · Accepted solution]

Hi Johan,

I can get 0.0 if I changed the datatype to float but how am I going to fix the decimal places to 2?

I got the figures in different decimal places:

0.00 becomes  0.0

12.30 becomes 12.3

12.56 becomes 12.56

3.35 becomes 3.3500000000000001

I have tried CAST(T1.Debit as numeric(19,2)) still showing the same

Thanks & BR,

Leng

[Johan_H · Accepted solution]

Hi Leng,

You can use the ROUND() function.

Regards,

Johan

[former_member1133820 · Accepted solution]

Hi John,

My problem is with the leading zero and not the decimal places. So, how to overcome the .00 problem in my stored procedure with ROUND() function?

Thanks,

Leng

[Johan_H · Accepted solution]

Hi Leng,

Hui Leng Chan wrote:

...I can get 0.0 if I changed the datatype to float but how am I going to fix the decimal places to 2...

Regards,

Johan

[former_member1133820 · Accepted solution]

Hi Johan,

Sorry for misleading.

I have tried to apply ROUND() to my query but still I'm getting 0.0 in my bar delimited file. I can get 0.00 in SSIS but cannot get it via bcp.

Thanks & BR,

Leng

[Johan_H · Accepted solution]

Hi Leng,

Ok, you could try another data type MONEY:

Debit money,

Credit money,

Balance money

Regards,

Johan

[former_member1133820 · Accepted solution]

Hi Johan,

If I use data type money, I can only get .0000.

Even I have tried to apply ROUND() also I get 4 decimal places & no leading zero.

Thanks & BR,

Leng

[Johan_H · Accepted solution]

Hi Leng,

When you tried Jim's suggestion, did you also change the field type of your temp table to nvarchar(21) ?

Regards,

Johan

[former_member1133820 · Accepted solution]

Hi Johan,

Thanks, guy.

You have just solved my mystery.

Finally I am managed to get 0.00 in SSIS & bar delimited file.

Thanks!

Best Regards,

Leng

[former_member1133820]

Hi,

I am able to get the results in correct format in SSIS but couldn't get it when I exported the file using bcp.

Thanks,

Leng

[edy_simon]

Hi Hui,

How do you export ?

Regards

Edy

[former_member1133820]

Hi Edy,

I export it using the bcp command.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##LRec" queryout C:\Data\L_Record.txt -t"|" -c -T'

Thanks & BR,

Leng

[Former Member]

Hi,

in the stored you ave to cast numeric(19,2) to Nvarchar(21)or(max) like this:

Select Cast(Cast(Debit as numeric(19,2)) as nvarchar(21) ) Debit

the sql set text like 0.00 so you dont have problem by number in the file.

Regards Alessandro

[former_member1133820]

Hi Alessandro,

Just tried it. still getting the same  .00 instead of 0.00.

Thanks & BR,

Leng

[Former Member]

Hi,

in the stored you have to set in (CREATE TABLE ##LRec) all numeric(19,2) column to nvarchar (21)

example:

Debit numeric(19,2), to Debit nvarchar (21)

Credit numeric(19,2),to Credit nvarchar (21)

Balance numeric(19,2) to Balanc nvarchar (21)

and in the query Select Cast(Cast(Debit as numeric(19,2)) as nvarchar(21) ) Debit .........etc...

try this.

regards, Alessandro

[Former Member]

Hi Hui,

try this Convert(nvarchar(21),convert(numeric(19,2),[YourFieldHere]))

Jim

[former_member1133820]

Hi Jim,

Still getting .00 instead of 0.00

Thanks,

Leng