Search code examples
sql-serverglobalization

Specify decimal places for currency in FORMAT call

My database contains fuel unit prices which typically end with a fractional penny value, such as $2.099 per gallon. I use the built-in FORMAT call in order to specify the locale. This doesn't allow me to use currency formatting with more than 2 decimal places. Is there a way to override this? Or am I stuck doing some custom formatting based on many many possible locales?

Consider this example:

DECLARE @fuelPrice FLOAT = 2.099
SELECT FORMAT(@fuelPrice, 'G', 'en-US') AS 'NoDollarSign'
      ,FORMAT(@fuelPrice, 'C', 'en-US') AS 'WrongDecimalPlaces'
      ,'$2.099' as 'WhatIWant'

which outputs:

NoDollarSign    WrongDecimalPlaces  WhatIWant
2.099           $2.10               $2.099
Solution

  • The Standard Numeric Format Strings allow you to specify a precision. Use C3 as the format instead:

    DECLARE @fuelPrice FLOAT = 2.099
SELECT FORMAT(@fuelPrice, 'C3', 'en-US')