format number with thousand-separator

is there a built in function in sql server 2005 that will format a number
with commas as the thousand-separator?

ie., here's what i want to do:
input=1000
output=1,000

or
input=1000000
output=1,000,000

if there isn't a built-in function, can someone point me to a
user-defined-function that might do the trick?

thanks so much,

jt

First of all, Aaron's answer was perfect given the amount of
description of the issue in the original posting. Since Aaron's reply
has now elucidated from you that your issue is in Microsoft Reporting
Services (the presentation layer in your system), there is no better
demonstration that his reply was perfect at pushing YOU to think
through your system and accurately identify where the problem is.

Now that we understand where the issue is, I found the following in BOL
in seconds:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/7852b4c7-3b45-406d-8e43-69c8cf2b710e.htm

Formatting Numeric and Date Values in a Report
You can specify a format for numeric and date values by updating the
Format property of its text box with a formatting string. For example,
you can set a text box for a numeric field to display the number as
currency. Reporting Services uses Microsoft .NET Framework formatting
strings or you can create a custom formatting string for the Format
property.
....
You can modify many of the format strings to include a precision
specifier that defines the number of digits to the right of the decimal
point. For example, a formatting string of D0 formats the number so
that it has no digits after the decimal point. You can also use custom
formatting strings, for example, #,###.

Have you tried defining a custom formatting string as per the manual?


On Jan 25, 3:23 pm, "JTL" wrote:
> BECAUSE IM PULLING DATA FOR MS REPORTING SERVICES AND THE ONLY FORMAT
> OPTIONS AVAILABLE ARE:
>
> 1234
> 1,234.00 (this one is close, but not what i want- this particular number
> will never have decimals so i don't want to waste space showing ".00")
> 1234.00
> 1.234123e+003
>
> its not helpful to reply when you don't have an answer, b/c then other
> people assume, by seeing a response next to the posting, that the problem
> has been solved.
>
> "Aaron Bertrand [SQL Server MVP]" wrote in messagenews:unj7UsLQHHA.4172@TK2MSFTNGP03.phx.gbl...
>
>
>
> > VB has format()
> > VBScript has formatnumber()
> > C# has string.format()
>
> > Why do you need to perform this presentation layer task in the database?
>
> > "JTL" wrote in message
> >
> >> is there a built in function in sql server 2005 that will format a number
> >> with commas as the thousand-separator?
>
> >> ie., here's what i want to do:
> >> input=1000
> >> output=1,000
>
> >> or
> >> input=1000000
> >> output=1,000,000
>
> >> if there isn't a built-in function, can someone point me to a
> >> user-defined-function that might do the trick?
>
> >> thanks so much,
>
> >> jt- Hide quoted text -- Show quoted text -