User Defined Function (UDF) to Convert Weight between Grams and Ounces in SQL Server

To create a User Defined Function (UDF) that converts weight from Grams to Ounces, that uses decimal(10,4) data type with precision 10 and scale 4 for input and output weight values, use the following command:

CREATE FUNCTION [dbo].[ufn_g2oz] (@g decimal(10,4))
RETURNS decimal(10,4)
AS
BEGIN
RETURN (@g * 0.035274)
END

To use this user-defined function (for example to convert 32.657 g to oz):

SELECT dbo.ufn_g2oz (32.657)

ufn_g2oz

To create a User Defined Function (UDF) that converts weight from Ounces to Grams, that uses decimal(10,4) data type with precision 10 and scale 4 for input and output weight values, use the following command:

CREATE FUNCTION [dbo].[ufn_oz2g] (@oz decimal(10,4))
RETURNS decimal(10,4)
AS
BEGIN
RETURN (@oz * 28.3495)
END

To use this user-defined function (for example to convert 24.167 oz to g):

SELECT dbo.ufn_oz2g (24.167)

ufn_oz2g

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">