February 12, 2003 at 3:31 pm
Simple question. I need to format an amount to enter the comas, periods, etc.
The amount shows in SQL like 49785.26 It should be 49,785.26.
Data type numeric.
I was trying to use formatstring, but it did not work. Any suggestions? Any help is alway very appreciated. Thank you.
The code for the store proc reads:
__________________________________
CREATE PROCEDURE dbo.spCINRpt2 (@CaseNumber varchar(7) , @RunJob as
Numeric(18,0)) AS
SELECT CASE_TAB.CASE_NO AS CASE_NO,
left(CASE_TAB.CASE_NO,2) + '-' + right(CASE_TAB.CASE_NO,5) AS CASE_NO_MASK,
CLAIM_TAB.CLAIM_NO as CLAIM_NO,
CLAIM_TAB.CRED_NO as CREDITOR_NO,
CREDITOR_NAM_TAB.CRED_NAME as CREDITOR_NAME,
CREDITOR_TYP_TAB.[desc] as Class,
IntRate = CASE WHEN LEN(CLAIM_TAB.INT_RATE) = 0 THEN 0.00 ELSE
ROUND(CLAIM_TAB.INT_RATE,2) END,
--CLAIM_TAB.NO_CHECK,
AllowedClaim = CASE WHEN CLAIM_TAB.NO_CHECK = 'O' THEN 'PAY DIRECT'
WHEN CLAIM_TAB.NO_CHECK = 'S' THEN 'SURR COLL'
WHEN CLAIM_TAB.NO_CHECK = 'X' THEN 'NOT FILED'
ELSE cast(ROUND(CLAIM_TAB.CLAIM_AMOUNT,2) as varchar(30)) END,
PercToBePd = CLAIM_TAB.PERCENT_ALLOWED ,
CLAIM_TAB.PRIN_PAID as PrinPaid,
CLAIM_TAB.INT_PAID as IntPaid,
--UnPdBalance = CLAIM_TAB.PRIN_OWED + CLAIM_TAB.INT_DUE
UnPdBalance = CASE WHEN CLAIM_TAB.NO_CHECK = 'X' OR CLAIM_TAB.NO_CHECK = 'O' OR CLAIM_TAB.NO_CHECK = 'S' THEN 0
ELSE CLAIM_TAB.PRIN_OWED + CLAIM_TAB.INT_DUE END
FROM CASE_TAB, CLAIM_TAB ,
CREDITOR_NAM_TAB,CREDITOR_TYP_TAB,CLOSE_CODE_TAB
WHERE CASE_TAB.CASE_NO = CLAIM_TAB.CASE_NO AND
CLAIM_TAB.CRED_NO = CREDITOR_NAM_TAB.CRED_NO AND
CREDITOR_TYP_TAB.CRED_TYPE = CLAIM_TAB.TYPE AND
CLOSE_CODE_TAB.CLOSE_CODE = CASE_TAB.CLOSE_CODE AND
CLAIM_TAB.TYPE <> 'G' AND
(CASE_TAB.CASE_NO = @CaseNumber)
ORDER BY CLAIM_TAB.CLAIM_NO
GO
_________________________________________
the format will be at this section:
cast(ROUND(CLAIM_TAB.CLAIM_AMOUNT,2) as varchar(30))
Thank you very much again and again.
February 12, 2003 at 4:05 pm
Have you checked out the convert function?
Try something like this:
declare @N numeric(18,2)
set @N = 49785.26
select convert(char,cast(@N as money),1)
set @N = 85.26
select convert(char,cast(@N as money),1)
set @N = 0.26
select convert(char,cast(@N as money),1)
set @N = 12312349785.26
select convert(char,cast(@N as money),1)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 12, 2003 at 5:13 pm
BOL 2000:
Note In client applications, some display formats do not translate appropriately for all locales. If you are supporting multiple locales or a locale different than that of the Analysis server, you should test the display formats in client applications on computers set to those locales.
Normally, try and do the formatting more downstream, not on the SQL box.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply