First: Yes I know number formatting shall not be done in data layer, but I'm working with an application I can't modify, nor can I format the result set in the application. I can only write a query.
For a SQL Server 2008 query I want to use space as thousand separator and also round to two decimals. Example: 123363.249999 should be displayed as 123 363,25.
I've tried:
SELECT REPLACE(CONVERT(varchar, CONVERT(money, 123363.249999), 1), '.00', '')
But that results in 123,363.25. I'm looking for nicer solution than just replacing comma with space and dot with comma. It would be nice to set the locale in the query.
For SQL Server 2008, I think your best bet for a "nicer" solution within SQL Server would be to write a custom CLR function to handle the formatting based on region/locale.
For SQL Server 2012 or later, the FORMAT function has been introduced.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments