SQL Server: Why does FORMAT return an empty string when the parameter is a zero?

Question

I'm dealing with a weird bug, when converting numbers to text.

Whenever the number is zero, FORMAT returns an empty string, instead of '0'

Is there a reason for this behavior?
Am I using the FORMAT function incorrectly?

SELECT FORMAT ( 1 , N'#.######################' )  /* result '1' */

SELECT FORMAT ( 1E0 , N'#.######################' )  /* result '1' */

SELECT FORMAT ( 1.0 , N'#.######################' )  /* result '1' */

SELECT FORMAT ( 0 , N'#.######################' )  /* result '' */

SELECT FORMAT ( 0E0 , N'#.######################' )  /* result '' */

SELECT FORMAT ( 0.0 , N'#.######################' )  /* result '' */

#.##### produces exactly the style we need, so we'd like to keep that if possible.

As a workaround, I'm manually checking for zeros - but this is annoying, and kinda unscalable.

Answer 1

The # doesn't fill in zeroes while 0 does. I think what you want is:

SELECT FORMAT ( 0 , N'0.######################' ) 

See https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings