Write a number with two decimal places SQL Server

Choosing between DECIMAL and NUMERIC: A precision game

When needing the exactness for calculations, the two champs to consider are DECIMAL and NUMERIC types. They are essentially twins in SQL Server. When defining them, you control the number of total digits (precision) and the digits after the decimal (scale):

-- "I need 10 digits in total, 2 of them after the decimal, please!"
DECIMAL(10,2)


Flaunt it different ways: Alternatives for formatting

Regional superstar: FORMAT function

When locale-specific needs creep up or when the presentation demands are high, FORMAT function walks in like a boss:

-- "We've got class, we talk in the local language!"
SELECT FORMAT(your_column, 'N2') AS local_number
FROM your_table;


'N2' format is a darlin' when you need a locale-specific number with two decimals. Speaking your audience's language never goes out of style!

The silent converter with CAST

When you have to change the way you handle data explicitly, CAST comes in stealthily:

-- "I may look like a number but at heart, I'm a string!" 
SELECT CAST(your_column AS NVARCHAR) AS disguised_number
FROM your_table;


Handy when joining forces with strings or when presentation layers are awaiting.

The blast from the past: STR function

Ever felt nostalgic for classic SQL or wanted more control over the total length and decimal digits? The STR function is your time machine:

-- "I love vintage! 10 characters in total, 2 after the decimal."
SELECT STR(your_column, 10, 2) AS retro_number
FROM your_table;


Just be cautious, STR may act snippy and truncate the value if it exceeds the defined length.

Pitfalls and Performances: Look before you leap!

Rounding off with CONVERT function

Notice how SQL Server rounds off to two decimal places when using CONVERT with DECIMAL(10,2):

-- "Round and round we go, where we stop, SQL Server knows!"
SELECT CONVERT(DECIMAL(10,2), 123.456) AS rounded_number;
-- Result: 123.46


This can have ripple effects so remember this when exactness is your game.

Consider performance and race ahead

While FORMAT is a convenient tool, it's not particularly a sprinter. When dealing with large datasets or in high-load scenarios, choose wisely. Every millisecond counts!