Generate restore scripts from backup history

Quite often people ask how to format numeric/money formats in more readable forms separated by commas. The same CONVERT function we use to format the date’s can be used for the same purpose.

Only money datatypes can be formatted like this, if you want to format a decimal/numeric fields, you need to cast them using the CAST function.

 

DECLARE @Amount MONEY
SELECT @Amount = 123456.7899
--No formatting
SELECT CONVERT(VARCHAR,@Amount,2)    -- 123456.7899
--Rounded but no formatting
SELECT CONVERT(VARCHAR,@Amount,0)    -- 123456.79    
--Formatted with commas
SELECT CONVERT(VARCHAR,@Amount,1)    -- 123,456.79