>>>>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?
>>>
>>>Why you didn't do this in your frontend?
>>
>>There are two reasons:
>>1) The sql query is built in a central code snippet that is being used in different places. I like to have the output formatted already in the result, so that all places that are using this query don't need to be changed.
>>2) The text is part of a larger text that would need to be processed in a post routing, but the result is very large and would slow down the process.
>
>You can write your own function like Rick suggested (but in TSQL :-) ) but that can slow down the query.
>Of course you can check the version of SQL Server to know if you should use that function or no.

Yes I was considering the second option, because the vast majority has a higher version already, and only a few clients may still use SQL 2008. So if those older versions don't display the amount properly we can tell them to upgrade their database.