The FORMAT() function is one to really remember, it saves you a lot of time.
Here are a few examples:
SELECT
FORMAT(12345678,'n') AS n
,FORMAT(12345678,'n0') AS n0
,FORMAT(12345678,'n0','nl-NL') AS n0NL
,FORMAT(0.8856,'p') AS p
,FORMAT(0.8856,'p1') AS p1
,FORMAT(12345678,'c') AS c
,FORMAT(12345678,'c1') AS c1
,FORMAT(12345678,'c1','nl-NL') AS c1NL
DECLARE @d DATETIME = '10/01/2011'
SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';
For more examples, please visit:
http://www.codeproject.com/Articles/610089/SQL-Servers-FORMAT-function