How to format numbers with MSSQL

  1. It's never a good idea to format number on the back-end.
  2. That's why there is a GUI/front-end. The screen or the report writer should do the formatting.
  3. Doing this type of conversions will prevent the database and the SQL Server from scaling.

Changing the formatting of number

USE adventureworks;
go

SELECT MAX(addressid) AS 'Plain',
       CAST(MAX(addressid) AS MONEY) AS 'Plain money',
       CONVERT(VARCHAR(15), CAST(MAX(addressid) AS MONEY), 1) AS 'US format',
       CONVERT(VARCHAR(15), CAST(MAX(addressid) AS MONEY), 2) AS 'ANSI format',
       CONVERT(VARCHAR(15), CAST(MAX(addressid) AS MONEY), 3) AS 'British format',
       CONVERT(VARCHAR(15), CAST(MAX(addressid) AS MONEY), 20) AS 'ODBC format',
       CONVERT(VARCHAR(15), CAST(MAX(addressid) AS MONEY), 126) AS 'ISO8601'
FROM person.address;
go
Plain       Plain money           US format       ANSI format     British format  ODBC format     ISO8601
----------- --------------------- --------------- --------------- --------------- --------------- ---------------
32521       32521.00              32,521.00       32521.0000      32,521.00       32,521.00       32521.0000

(1 row(s) affected)
  • Cast only does a straight conversion from one data type to another.
  • Convert allows you to apply a style. It's the style that give the periods/commas.
  • Microsoft convert format styles
  • The convert style is mostly used for date and times formats.