CODEWITHSUNDEEP
sqlsql-serversql-server-2016
Aaron Stainback
Aaron Stainback

Reputation: 3667

Is this a casting bug in SQL Server 2016 RC0?

Returns 45.2478

SELECT
    CAST(
        geography::STPointFromText( 'POINT(-81.2545 44.1244)', 4326 ).Lat + 1.12342342
        AS VARCHAR(50)
    )

Returns 4.524782342440000e+001

SELECT
    CONVERT(
        VARCHAR(50),
        geography::STPointFromText( 'POINT(-81.2545 44.1244)' , 4326 ).Lat + 1.1234234244,
        2
    )

According to the "Truncating and Rounding Results" section on this page it looks like CAST should never truncate a float but it's doing it in this case.

Upvotes: 1

Views: 150

Answers (3)

SQL Police
SQL Police

Reputation: 4206

Quote from you: "it looks like CAST should never truncate a float but it's doing it in this case."

Attention: The example you write here does not use float, but decimal. These are two different types, and it is very important to distinguish between them. decimalhas by default 6 digits, that explains this behaviour.

Everything is in check. SQL Server is one of Microsoft's most stable software pieces, you will hardly find a bug there ;)

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

The link to the docs that you included in the question has an answer.

CAST is the same as CONVERT without explicitly specifying the optional style parameter.

float and real Styles

Value:  0 (default)
Output: A maximum of 6 digits. Use in scientific notation, when appropriate.

So, when you use CAST it is the same as using CONVERT with style=0. Which returns a maximum of 6 digits, i.e. result is rounded to 6 digits.

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93734

It is due to the style part you mentioned in CONVERT function

Your query with style = 2 

SELECT CONVERT(VARCHAR(50),geography::STPointFromText('POINT(-81.2545 44.1244)',4326).Lat+1.1234234244,2)

Result : 4.524782342440000e+001

But when I remove the Style part from Convert function

SELECT CONVERT(VARCHAR(50),geography::STPointFromText('POINT(-81.2545 44.1244)',4326).Lat+1.1234234244)

Result : 45.2478

which is same as CAST function

FYI, Style 2 is used to format dates in yy.mm.dd format

Upvotes: 1

Related Questions