Transact-SQL Cast or Convert

[adman29]

Hey Y'all,

I've been working on a SQL Project and I missed the lesson on Cast and Convert

Would anyone know how to Cast a calculated field into a decimal that has only 2 decimal places?

 

My current code is basically:

SELECT P.ProductID, P.Name, (P.ListPrice-P.StandardPrice)/P.StandardPrice AS Profit Margin
FROM Produciton.Product P
//A whole bunch of Joins down here)

Lemme know if you have questions

[adman29]

Okay, I figured it out, sorry for the quick turnaround, but if anyone needs the info for future reference:

Select P.ProductModelID, P.Name, CAST(((P.ListPrice - P.StandardCost)/P.StandardCost) AS DECIMAL(18,2)) AS ProfitMargin
From Production.Product P
Left JOIN Production.ProductSubcategory PS
		ON P.ProductSubcategoryID = PS.ProductSubcategoryID
Right Join Production.ProductCategory PC
		ON PC.ProductCategoryID = PS.ProductCategoryID
Where PC.ProductCategoryID = 1
ORDER BY ProfitMargin DESC

 

[APileofRocks]

On 10/21/2019 at 10:17 AM, adman29 said:

Okay, I figured it out, sorry for the quick turnaround, but if anyone needs the info for future reference:

Select P.ProductModelID, P.Name, CAST(((P.ListPrice - P.StandardCost)/P.StandardCost) AS DECIMAL(18,2)) AS ProfitMargin
From Production.Product P
Left JOIN Production.ProductSubcategory PS
		ON P.ProductSubcategoryID = PS.ProductSubcategoryID
Right Join Production.ProductCategory PC
		ON PC.ProductCategoryID = PS.ProductCategoryID
Where PC.ProductCategoryID = 1
ORDER BY ProfitMargin DESC

 

If it turns out you need standard thousand separators as well, you can use CAST to money datatype within a CONVERT like so:

 

SELECT convert(varchar,cast(((P.ListPrice - P.StandardCost)/P.StandardCost) as money), 1) as 'ProfitMargin'

 

However, once converted to varchar, you lose the ability to perform calculations. So this is more for presentation purposes - don't convert out of an INT datatype until you're done performing calculations.