garethtnash
asked on
Select Format Decimal & Currency
Hello,
I have the following Stored Procedure....
Which is producing results like -
20333.333333
Ideally what I want to do is have the resuilts like
£20333.33
So add the £ and only show 2 characters after the period...
Appreciate any help you can offer -
Thank you
I have the following Stored Procedure....
CREATE PROCEDURE [dbo].[LocalRetainerAverage]
(
@Locality int
)
AS
BEGIN
SET NOCOUNT ON;
Select
AVG(RetainerValue) As Retainer,
AVG(FeeCommission) AS FeeCommission,
AVG(TotalRetainer) AS Total
From dbo.RetainerSurvey
Where Locality = @Locality
END
GO
Which is producing results like -
20333.333333
Ideally what I want to do is have the resuilts like
£20333.33
So add the £ and only show 2 characters after the period...
Appreciate any help you can offer -
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656
SELECT '£ '+ CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57
--Formatted with commas
http://www.tek-tips.com/faqs.cfm?fid=6157
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Guys,
Thanks for all the responses, and apologies for the delayed response. One last question, what about if I wanted to round the number so that -
1,322,323.67
becomes -
1,322,324
thanks
Thanks for all the responses, and apologies for the delayed response. One last question, what about if I wanted to round the number so that -
1,322,323.67
becomes -
1,322,324
thanks
ASKER
thank you
>> One last question, what about if I wanted to round the number so that -
>> 1,322,323.67
>> becomes -
>> 1,322,324
Was this comment helpful?
>> 1,322,323.67
>> becomes -
>> 1,322,324
declare @v money
set @v = round (1322323.67, 0) -- 1,322,324
select '£ '+ convert(varchar, @v, 1);
Was this comment helpful?
SELECT '£'+ STR(@MyVar, 10, 2)