Format sql column when using select

Question

I have the following query to select record but i wan to format the column on the result set.

SELECT 
    COALESCE(dbo.tblMitchellLandscapeID.PatchSize,0) as PatchSize,
    dbo.tblMitchellLandscape.MitchellLandscapeName
FROM tblMitchellLandscapeID
INNER JOIN dbo.tblMitchellLandscape
      ON dbo.tblMitchellLandscapeID.MitchellLandscapeID=dbo.tblMitchellLandscape.MitchellLandscapeID
WHERE AssessmentVersionID = @AssessmentVersionID

"PatchSize" is a decimal value so it stored always like two decimals "15.10". All i trying to format to one decimal when the select statement is executed i wan to populate the result set like "15.1" rather than 15.10.

Answer 1

You can just cast it to the format you want:

SELECT CAST(COALESCE(li.PatchSize, 0) as decimal(5, 1)) as PatchSize,
       l.MitchellLandscapeName
FROM tblMitchellLandscapeID li INNER JOIN
     dbo.tblMitchellLandscape l
     ON li.MitchellLandscapeID = l.MitchellLandscapeID
WHERE AssessmentVersionID = @AssessmentVersionID;

Notice the query is also easier to read (and write) if you use table aliases.