Convert Grams to Kilograms in SQL Server

Tevin
 
on Jun 07, 2021 11:09 PM
1208 Views

Change Specific value to kg conversion

I Would like to convert the column 'VarianceToSysproGram' to Kilogram as it is currently displaying it in grams.

This is how the value in the column display: '0.90000000'

SELECT s.StockCode , 
i.LongDesc,
(s.PartWeightGram / 1000) AS 'Part Weight kg' , 
(s.SprueWeightGram / 1000) AS 'Sprue Weight kg' , 
STR(s.TolerancePercentage,10,2) AS 'TolerancePercentage' , 
s.BomWeightKG AS 'Bom Weight kg', 
s.CapturedDateTime ,
s.PartWeightGram + (s.SprueWeightGram / 2) - s.BomWeightKG AS 'VarianceToSysproGram' ,
(((((s.PartWeightGram + s.SprueWeightGram  / 2))) - (s.BomWeightKG * 1000)) / (s.BomWeightKG * 1000))  * 100 AS 'VarianceToSysproPct',
IsOutOfSpec
FROM tblComponentWeightCheck s
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON  s.StockCode = i.StockCode
WHERE CONVERT(Date, s.CapturedDateTime) Between '2021-04-01' AND '2021-05-24'
And Deleted = 'False'
ORDER BY CapturedDateTime, s.StockCode

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 08, 2021 12:06 AM

Hi Tevin,

Multiply the value with 1000 to convert the gram to kilogram.

Check the sample query.

SQL

DECLARE @Value AS DECIMAL(18,8)
SET @Value = '0.0009'
SELECT @Value * 1000

Your modified query.

SELECT s.StockCode,
i.LongDesc,
(s.PartWeightGram / 1000) AS 'Part Weight kg',
(s.SprueWeightGram / 1000) AS 'Sprue Weight kg' ,
STR(s.TolerancePercentage,10,2) AS 'TolerancePercentage',
s.BomWeightKG AS 'Bom Weight kg',
s.CapturedDateTime,
(s.PartWeightGram + (s.SprueWeightGram / 2) - s.BomWeightKG) * 1000 AS 'VarianceToSysproGram',
(((((s.PartWeightGram + s.SprueWeightGram  / 2))) - (s.BomWeightKG * 1000)) / (s.BomWeightKG * 1000))  * 100 AS 'VarianceToSysproPct',
IsOutOfSpec
FROM tblComponentWeightCheck s
LEFT JOIN [Mercury].[EncoreCompanyA].dbo.InvMaster i ON  s.StockCode = i.StockCode
WHERE CONVERT(Date, s.CapturedDateTime) Between '2021-04-01' AND '2021-05-24'
And Deleted = 'False'
ORDER BY CapturedDateTime, s.StockCode