format - sqlcmd for SQL Server removes zeros to the left of the decimal point

Question

We're trying to read data from a SQL Server table for inserting it into a .csv using sqlcmd in Linux OS.

Unfortunately when we try to read the float values from table like

-0.373783

then in files we get

-.373783

but we would like to get on file -0.373783 as on the database table.

We don't understand why sqlcmd remove leading zeros, I mean to the left of the decimal point, the same select query on SSMS return the right number representation.

We tried to use the following statement

SELECT CAST(floatField AS Numeric(9,6))

but we keep getting the number without the zeros before the decimal point.. we are going crazy!

Thanks in advance for your suggestion

question from:https://stackoverflow.com/questions/65907397/sqlcmd-for-sql-server-removes-zeros-to-the-left-of-the-decimal-point

Answer 1

Please try the following.

The FORMAT() function is using .Net CLR formatting. Combination of '0's and '#'s will give you what you are looking for.

SQL

DECLARE @var DECIMAL(10,6) = -0.373783;

SELECT @var AS [Before]
    , CAST(@var AS VARCHAR(12)) AS [After]
    , FORMAT(@var,'###0.######') AS [Formatted];