Converting INT to a dollar amount 2

[KingRichard3]

Is there an easy way to convert
a double: 1234.56
to this: $1,234.56
?

Thanks,
Rick

 

[Catadmin]

Select Cast(<intVariable> as money) as Amount
From table

is the first thing I come up with. Since they're both numbers, using Convert(money, <intvariable>)should work too.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[KingRichard3]

Cat,

Thanks - but that doesn't do the comma formatting with the dollar sign...

Thanks,
Rick

 

[Catadmin]

I'm the queen of quick corrections today. Looking at the numbers in QA and EM, there is still no dollar sign appended to the beginning.

If you want to see the dollar sign in the field, you'll need to do a Cast such as (using the pubs database):

select '$'+ Cast(price as varchar(10))
from titles

The choice of Char, Varchar, NVarchar at this point is up to you. As is the number of available spaces for your conversion. Essentially, the above statement transforms the number into characters and appends the '$' character to the front of it.

I hope that clarifies the matter for you.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[KingRichard3]

...I'm the king of quick follow-ups today.

Any easy way to add the commas?
Rick

 

[Catadmin]

Caught your post about the comma. The Convert function is supposed to allow you to choose a style for the comma, but I can't get it to work. Lookup Cast And Convert in books online. It says if you use:

Select Convert(money, intvalue, 1) from tablename

Style 1 should show the commas every 3 digits on the left side of the decimal. But for some reason, my QA won't do it. I've even tried style 2 and style 0 just in case BOL had the styles mixed up, with no success.

However, the Convert function doesn't add your $ to the front. I think you'll have to use the Right/Left or RTrim/LTrim functions to split the amount and do a + ',' + between every three digits.

Anyone else have success with Convert and money styles?




Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

This will work for your particular number, but you will need case statements to insert a comma every three digits.

select substring(ltrim(rtrim(cast(amount as varchar))),1,1)+','+
substring(ltrim(rtrim(cast(amount as varchar))),2,len(amount))
from table

Tim

 

[mrdenny]

This FAQ should answer your questions. faq183-4418

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JamesLean]

The important thing to remember about using CONVERT to get formatted currency values is that it only works with the money data type. So there's really no need to be messing about with UDFs and SUBSTRINGs!

DECLARE @num decimal(6, 2)
SET @num = 1234.56

SELECT '$' + CONVERT(varchar(20), CAST(@num AS money), 1)

Denny, maybe you could update that FAQ?

--James

 

[KingRichard3]

James,

Excellent, excellent, excellent - thanks!!!

Thanks, Mr. Denny, too!
Rick

 

[mrdenny]

The only problem that I have with the money data type is that is has 4 decimal places, not 2. This can throw of calculations.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrdenny]

The only problem that I have with the money data type is that is has 4 decimal places, not 2. This can throw of calculations.

My udf can also be used to format numbers that have nothing to do with money.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Yeah, but the last two digits (of the four after the decimal point) can be trimmed off with RTrim or rounded up/down with Round, right?



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[mrdenny]

This is true. But It's just easier to use a data type with only 2 to the right.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

The only problem that I have with the money data type is that is has 4 decimal places, not 2. This can throw off calculations.

Having 4 decimals is the best way to work with monetary amounts. It would be a mistake to use only 2 decimals.

[Blue]DECLARE[/Blue] @m [Blue]AS[/Blue] [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @m10 [Blue]AS[/Blue] [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @n10 [Blue]AS[/Blue] [Blue]numeric[/Blue][Gray]([/Gray]10[Gray],[/Gray]2[Gray])[/Gray]
[Blue]DECLARE[/Blue] @a [Blue]AS[/Blue] [Blue]money[/Blue]
[Blue]DECLARE[/Blue] @n [Blue]AS[/Blue] [Blue]numeric[/Blue][Gray]([/Gray]10[Gray],[/Gray]2[Gray])[/Gray]
[Blue]DECLARE[/Blue] @i [Blue]AS[/Blue] [Blue]int[/Blue]
[Blue]SET[/Blue] @a[Gray]=[/Gray]12.53
[Blue]SET[/Blue] @m[Gray]=[/Gray]0
[Blue]SET[/Blue] @n[Gray]=[/Gray]0
[Blue]SET[/Blue] @m10[Gray]=[/Gray]0
[Blue]SET[/Blue] @n10[Gray]=[/Gray]0
[Blue]SET[/Blue] @i[Gray]=[/Gray]1
[Blue]WHILE[/Blue] @i[Gray]<[/Gray][Gray]=[/Gray]100 [Blue]BEGIN[/Blue]
   [Blue]SET[/Blue] @m10[Gray]=[/Gray]@m10[Gray]+[/Gray]@a[Gray]*[/Gray].1
   [Blue]SET[/Blue] @n10[Gray]=[/Gray]@n10[Gray]+[/Gray]@a[Gray]*[/Gray].1
   [Blue]SET[/Blue] @n[Gray]=[/Gray]@n[Gray]+[/Gray]@a
   [Blue]SET[/Blue] @m[Gray]=[/Gray]@m[Gray]+[/Gray]@a
   [Blue]SET[/Blue] @i[Gray]=[/Gray]@i[Gray]+[/Gray]1
[Blue]END[/Blue]
[Blue]SELECT[/Blue] @n[Gray]*[/Gray].1[Gray],[/Gray]@n10[Gray],[/Gray]@m[Gray]*[/Gray].1[Gray],[/Gray]@m10

The above code demonstrates that if you were to use a query to calculate a commission of 10% of sales you would have a problem depending on how you write the query.

[Blue]SELECT[/Blue] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]SalesAmount[Gray])[/Gray][Gray]*[/Gray].1[Gray],[/Gray] [Fuchsia]SUM[/Fuchsia][Gray]([/Gray]SalesAmount[Gray]*[/Gray].1[Gray])[/Gray]

I will grant you that the error could be avoided, but it could easily be overlooked. There's a reason why they made a money datatype have 4 decimals. Many reworld computations need all 4 to avoid common errors.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

Perhaps it would be best to just stop using pennies. We could drop some precision and save a few bytes here and there.

http://www.sfgate.com/cgi-bin/article.cgi?file=/chronicle/archive/2001/07/19/MN108576.DTL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[donutman]

Hehe, at my bakery we haven't used pennies (except those we receive) for the last 6 years.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[miq]

Hi,

mrdenny, i was playing with your function and ended up this:

ALTER Function FormatNumber 
(@RawNumber numeric(32,2) = 0,
    @ShowDecimal as bit = 0,
[COLOR=red]
    @CurrencySymbol as char(1) = ''  
[/color]
)
returns varchar(200) AS
BEGIN
    declare @FormattedNumber as varchar(100)
    declare @AfterDecimal as varchar(100)
    declare @i as int
    declare @j as int
    set @i = 0
    set @j = 0
    set @FormattedNumber = convert(bigint, @RawNumber)
    set @AfterDecimal = @RawNumber
    if charindex('.', @AfterDecimal) <> 0
    BEGIN
        set @AfterDecimal = '.' + right(@AfterDecimal, len(@AfterDecimal)-charindex('.', @AfterDecimal))
    END
    ELSE
    BEGIN
        set @AfterDecimal = ''
    END
    
    
    while @i <> len(@FormattedNumber)
    BEGIN
        if @j = 3
        BEGIN
            set @j = -1
            set @FormattedNumber = left(@FormattedNumber, len(@FormattedNumber)-@i) + ',' + right(@FormattedNumber, @i)
        END
        set @j = @j + 1
        set @i = @i + 1
    END
[COLOR=red]
    IF @currencysymbol  != ''
        set @FormattedNumber = @currencysymbol + @FormattedNumber
[/color]
    if @ShowDecimal <> 0
        set @FormattedNumber = @FormattedNumber + @AfterDecimal
    return @FormattedNumber
END

B.R,
miq

 

[donutman]

I smell a 1M row test of a speed test between a UDF and and James' inline format statement. Gmmastros are you up for it? I'll bet on a 6 to 1 speed difference in this case.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

donutman, that's mean. I was actually trying to get some work done today.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom