March 2, 2005 at 5:52 am
When working with money how can one set up so that every thousands unit is shown seperate?
IE $1,000,000.00
If this isn't possible in data type money what else can one use and how do i set it up?
Thank you in advance.
March 2, 2005 at 6:09 am
Monty,
You will need to write a custom function to properly format the string. I think this is because of the various different money formats throughout the world. Some sep with commas, others decimals.
You will basically have to write a CASE LEN(fieldA) WHEN <= 3 THEN fieldA WHEN 4 THEN CAST(LEFT(fieldA, 1) AS CHAR(1)) + ',' + CAST(RIGHT(fieldA, 3) AS CHAR(3)) END
as an example....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 3, 2005 at 1:15 am
You could try using the convert function with the 3rd parameter (the style parameter) set to 1 - see Books on Line for info.
eg
declare @mymoney money
set @mymoney = 1234567890
select '$' + convert(varchar(24),@mymoney,1)
result set is:
-------------------------
$1,234,567,890.00
Regards,
Stu
March 3, 2005 at 2:55 am
Apart from this being a merely presentational issue, have you searched the script section here already. I think Antares686 posted something useful there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 3, 2005 at 8:12 am
I have a UDF to format values of type DECIMAL, and have adapted it here for type MONEY. Modify as necessary.
DROP FUNCTION dbo.FormatMoney
GO
CREATE FUNCTION dbo.FormatMoney
(
@num money,
@decPlaces int
)
RETURNS varchar(20)
AS
BEGIN
DECLARE @cnum varchar(50), @len int, @pos int, @fraction varchar(50)
DECLARE @numCommas int
SET @cnum = CONVERT(varchar(50), @num)
SET @fraction = ''
SET @pos = CharIndex('.', @cnum, 1)
IF @pos > 0
BEGIN
SET @fraction = Substring(@cnum, @pos + 1, 50)
SET @cnum = Left(@cnum, @pos - 1)
END
SET @len = Len(@cnum)
SET @numCommas = (@len-1) / 3
WHILE @len > 3
BEGIN
SET @cnum = Stuff(@cnum, @len-2, 0, ',')
SET @len = @len - 3
END
IF @decPlaces > 0
BEGIN
SET @fraction = Left(@fraction + Replicate('0', @decPlaces), @decPlaces)
END
ELSE
SET @fraction = ''
IF @fraction <> ''
SET @cnum = @cnum + '.' + @fraction
RETURN '$' + @cnum
END
GO
--- Tests
print dbo.FormatMoney(0,2)
print dbo.FormatMoney(1,2)
print dbo.FormatMoney(12,2)
print dbo.FormatMoney(123,2)
print dbo.FormatMoney(1234,2)
print dbo.FormatMoney(12345,2)
print dbo.FormatMoney(123456,2)
print dbo.FormatMoney(1234567,2)
print dbo.FormatMoney(1234567,0)
print dbo.FormatMoney(1234567,1)
print dbo.FormatMoney(1234567,2)
print dbo.FormatMoney(1234567,3)
print dbo.FormatMoney(12345678,2)
print dbo.FormatMoney(123456789,2)
print dbo.FormatMoney(1234567890,2)
print dbo.FormatMoney(2147483647,2)
March 3, 2005 at 8:37 am
I prefer not to use the money data type, I use decimal instead. I've had some funky stuff happen in the past with Access and Crystal Reports formatting the field in a way other than what I want. I do the formatting when I output or display the field, and the UDFs and scripts mentioned previously will help you there.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 3, 2005 at 8:49 am
Can't comment on the formatting of MONEY. However, MONEY gets also funky when division and/or multiplications are involved in calculations with MONEY:
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/@m2
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0028 .0029
(1 row(s) affected)
Both are calcualted correct according to thier specifications. However, the MONEY result is off by more than 3% !!! from the "correct" result.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 3, 2005 at 9:09 am
I agree with Frank and Wayne - use decimal, or even float, and format the display as currency when needed.
--Mike
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply