Convert to 2 decimals and keep leading zero [closed]

The following piece of code is resulting in a value with 4 decimals.

``````COALESCE (SUM( CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' and PLL_REVENUETYPE = 'OTHER' then CAST(ROUND(PLL_AMOUNT,2) as decimal (9,2))
WHEN PLL_REVENUETYPE    = 'TAX' THEN CAST(ROUND(PLL_TAXAMOUNT,2) as decimal (9,2))
WHEN PLL_ISINCLUSIVETAX = '-'   THEN CAST(ROUND(PLL_NETAMOUNT,2) as decimal (9,2))
ELSE CAST(ROUND(PLL_NETAMOUNT,2)as decimal (9,2)) END ), 0) * -1.00 AS AMOUNT
``````

I found many suggestions on the web, but I haven't been able to round the amount with 2 decimals.

Also, when I do a SQMCMD to export the query to a CSV, it will drop the leading zero if the amount is 0.00

Any help would be appreciated.

• Regarding the leading zero, there's no way for SQL Server to return a numeric value with a leading zero unless you return that value as a string. And as far as the number of decimals, the answer by @Mordechai should be the way to go, but if you are still experiencing issues, as seems to be indicated by your comment under the answer, then it would be hard to resolve them without a clear example demonstrating the problem. Dec 24, 2020 at 9:50

You don't need both cast and round. For example:

``````declare @val decimal(9,2) = '849.909839'
select @val [val]
, cast('849.909839' as decimal(9,2))*-1.00 [cast_first]
, cast(convert(float,'849.909839') *-1.00 as decimal(9,2)) [cast_last]
``````

If you run this, you can see the problem. It's in the else clause. you should do a cast after you multiply by -1.00.

• Thanks @Mordechai, based on your recommendation, I've changed the piece of code to: , COALESCE (CAST(SUM(CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' AND PLL_REVENUETYPE = 'OTHER' THEN PLL_AMOUNT WHEN PLL_REVENUETYPE = 'TAX' THEN PLL_TAXAMOUNT WHEN PLL_ISINCLUSIVETAX = '-' THEN PLL_NETAMOUNT ELSE PLL_NETAMOUNT END) * -1.00 AS DECIMAL(24,2)), 0) AS AMOUNT But unfortunately, the result is still 6 decimals. Dec 19, 2020 at 2:40
• here is a simplified version of your code - DECLARE @var FLOAT = 30869862812595.45869 SELECT COALESCE(CAST(@var* -1.00 AS decimal(24,2)), 0). how many places after the decimal point does this return on your system? Dec 22, 2020 at 15:09
• I'm not sure what you mean by the `ELSE` clause, the OP's `ELSE` branch has the same rounding and conversion as every `WHEN...THEN` branch in that `CASE`, so the result of the `CASE` expression itself is always `decimal(9,2)`. But I agree with you completely that in order for the final result to have that data type, they should do the conversion after all the calculations, and the OP does indeed have multiplication by -1.00 at the outermost level. (It's not in the `ELSE` clause, it's actually outside everything, including the `COALESCE` function.) Dec 24, 2020 at 9:52