2

I have a hard time understanding why casting float to varchar returns -0 instead of 0

DECLARE @a FLOAT

SET @a = 0
SET @a= @a * -1

SELECT @a --return 0

DECLARE @b as VARCHAR(10)
SET @b=@a

SELECT @b -- this return -0

As you can see above code will return -0. My current workaround is to add another cast as decimal, like so:

SET @a= cast(@a AS decimal(10,2)) *-1

My question is: Why is that code returning -0 instead of 0?

SQL Fiddle

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
Misiu
  • 4,738
  • 21
  • 94
  • 198
  • 1
    Probably because of this: https://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples – Tab Alleman Jul 30 '18 at 13:56
  • 4
    Because, in IEEE-754 floating-point, [signed zero](https://en.wikipedia.org/wiki/Signed_zero) is a thing, and the result of `-1 * 0e` is indeed `-0`. It's usually presented as just `0` by clients -- but not by the explicit conversion, apparently. If you want more reliable results when converting things to `VARCHAR`, use `FORMAT` (e.g. `SELECT FORMAT(@a, 'G')`). – Jeroen Mostert Jul 30 '18 at 13:58
  • Also, for fun and profit, try `SELECT CONVERT(BINARY(8), -0e), CONVERT(BINARY(8), 0e), CONVERT(BINARY(8), -0.), CONVERT(BINARY(8), 0.)`, which demonstrates that signed zero is a `FLOAT` thing, but not a `DECIMAL` thing. – Jeroen Mostert Jul 30 '18 at 14:14

1 Answers1

2

When you do SELECT @a, you're returning a result set to some client (e.g. SQL Server Management Studio) that consists of a single column of type float. It is then up to that tool to convert that float into a string using whatever options it deems appropriate in it's programming environment.

When you assign @a to @b, you force an implicit conversion from float to varchar(10). SQL Server decides how to perform this conversion and then you send that data to the client tool in a second result set. It's likely that the client tools will do little or no manipulation because it's already, to some extent, a string.

So you've got two different things running conversions. SQL Server Management Studio1 will usually pick conversions similar to the ones that SQL Server itself would pick but it's by no means guaranteed, as you're observing with your test case here.

1Assumed as the usual starting point for people asking such questions. But equally valid for SQL Fiddle. The @b conversion to a string was controlled by SQL Server. The @a conversion was performed by some code within the SQL Fiddle application. This may be something built in to the language/framework SQL Fiddle is built in or some custom code. Doesn't matter. Key difference is that it's different code written by (probably) different authors with (potentially) different views on what makes a particular conversion the sensible default choice.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448