Great, that does it. I use convert for dates, but haven't found this one.
>You can use CONVERT ( ,,1))
>
>CONVERT ( varchar(20) , CAST(1234567.89 AS money ), 1 ) ;
>
>
>>I used RTRIM(FORMAT(1234567.89, 'N', 'en-US')) to format a number with thousand separators and decimals. However this works only since SQL Server 2012 and above.
>>
>>So 1234567.89 should be "1,234,567.89" or 10.00 should be "10.00"
>>
>>Best alternative I found is LTRIM(STR(1234567.89,14,2)), but this of course does not produce thousands separators.
>>
>>I tried RTRIM(REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,1234567.89),1), '.00','')), but this would cut off decimals if they are #.00.
>>
>>Is there another way to have the best of both worlds?
Christian Isberner
Software Consultant