Why is this Sql Server CAST(..) rounding a Decimal to a VarChar ?

Question

Hi folks,

I'm trying convert some decimals to varchar, but they are getting rounded.

Can someone tell me why?

declare @UpperLeftLatitude DECIMAL,
    @UpperLeftLongitude DECIMAL,
    @BottomRightLatitude DECIMAL,
    @BottomRightLongitude DECIMAL

SET @UpperLeftLatitude = 38.663
SET @UpperLeftLongitude = -122.857
SET @BottomRightLatitude = 37.795
SET @BottomRightLongitude = -121.219


DECLARE @SearchRectangleString VARCHAR(MAX);
SET @SearchRectangleString = 'POLYGON((' + CONVERT(VARCHAR(50), @UpperLeftLatitude) + ' ' + CAST(@UpperLeftLongitude AS VARCHAR(50)) + ',' 
    + CAST(@BottomRightLatitude AS VARCHAR(50)) + ' ' + CAST(@UpperLeftLongitude AS VARCHAR(50)) + ',' 
    + CAST(@BottomRightLatitude AS VARCHAR(50)) + ' ' + CAST(@BottomRightLongitude AS VARCHAR(50)) + ',' 
    + CAST(@UpperLeftLatitude AS VARCHAR(50)) + ' ' + CAST(@BottomRightLongitude AS VARCHAR(50)) + ',' 
    + CAST(@UpperLeftLatitude AS VARCHAR(50)) + ' ' + CAST(@UpperLeftLongitude AS VARCHAR(50)) + '))';

    SELECT @SearchRectangleString

-------------------
POLYGON((39 -123,38 -123,38 -121,39 -121,39 -123))

(1 row(s) affected)

NOTE: Yes, I know my Lat/Longs are the wrong way around. I'll switch em soon.

Answer 1

It's because your decimals are not specified with a length. They aren't storing any decimal places.

Try the following:

 DECLARE @test DECIMAL, @test2 DECIMAL(8,4)
 SET @test = 12.3456
 SET @test2 = 12.3456

 SELECT @test, @test2

Answer 2

As ck mentioned it gets rounded...

When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Source: ROUND (T-SQL)