The # format specifier suppresses any insignificant zeros while the 0 format specifier does not.īut perhaps more importantly (for the purposes of this article), the 0 format specifier allows you to add insignificant zeros if they aren’t present in the original number. We can see that there’s a difference between using # and 0 in the format string. Here’s an example of using custom format strings: SELECT Custom format strings allow you to specify a format that might not be supported by a standard format string. It’s been rounded up, just like when we converted the number in the other example.Īnother way to do it is to use a custom format string. The same technique can be used to reduce the decimal places to two, from a number with more decimal places: SELECT FORMAT(275.4567, 'N2') In this case, N is for number and 2 is for the number of decimal places (you can increase or decrease this as required). The N2 part is referred to as a format string. This function actually converts the number to a string, so technically, the result is not a numeric type. Here, we use CONVERT() to do the same thing – convert the number to decimal: SELECT CONVERT(DECIMAL(5, 2), 275) Īnother way to format a number with two decimal places is to use the FORMAT() function: SELECT FORMAT(275, 'N2') One thing to remember though, is if you’re reducing the number of decimal places from a number with more than two decimal places, then you could end up with the second decimal place being rounded up: SELECT CAST(275.4567 AS DECIMAL(5, 2)) We can use this method even if the number is already a decimal value but with more decimal places.
Here’s an example of using CAST(): SELECT CAST(275 AS DECIMAL(5, 2)) Two functions that can do this for us is CAST() and CONVERT(). The most obvious way to do it is to convert the number to a decimal type. When using T-SQL with SQL Server, we can format numbers using various methods, depending on our desired format.īelow are four functions that can be used to format a number to two decimal places in SQL Server.
0 kommentar(er)
