Hi,

Does anybody have an idea how to cut off the decimals of a money
variable, but to keep the thousand separator?
e.g. change 12,345.00 into 12,345
Converting it into a numeric or integer looses the thousand separator
as well (becoming 12345).

Thanks,
Linda.

I do it by printing/selecting character values. With 12.5.0.3:

select Y = left (convert (char (N+3), convert (money, X), 1), N)

where X is an integer value and N is the display width including commas.
I'd prefer it it there were an additional argument for the str function to
do this. I put this on the product_futures forum in May and there were some
cogent responses for not doing this (e.g., locales issues since periods are
used instead of commas in numerous countries).

"lhusmann" <lhusmann (AT) hotmail (DOT) com> wrote

You can convert the money variable to a character string, which allows the
comma separator to be preserved. It's probably not widely known, but a
style parameter can be used when converting money to a character datatype
(style parameters are commonly used in datetime conversions).

Here's some SQL that illustrates the usage of the style parameter for money
conversions:

set nocount on
declare @num money, @str_num varchar(10)
select @num = $12345

/* convert to character without style parameter */

select @str_num = convert(varchar, @num)
print '@str_num without style param = %1!', @str_num

/* convert to character with style parameter */

select @str_num = convert(varchar, @num, 1) -- style param
print '@str_num with style param = %1!', @str_num

The results show that using the style parameter preserves the comma:

@str_num without style param = 12345.00
@str_num with style param = 12,345.00

To strip the decimal and cents requires some extra code:

select substring(convert(varchar, @num,1), 1,datalength(convert(varchar,
@num)) -2)

------------------------------
12,345


Andy


On 10/22/03 5:49 AM, in article
7ccf6d45.0310220449.4e7eb64b (AT) po...OT) google.com, "lhusmann"
<lhusmann (AT) hotmail (DOT) com> wrote: