User Defined Function (UDF) to Convert Length between Kilometers and Miles in SQL Server

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

CREATE FUNCTION [dbo].[ufn_km2mi] (@km decimal(10,3))
RETURNS decimal(10,3)
AS
BEGIN
RETURN (@km * 0.621371)
END

To use this user-defined function (for example to convert 52.231 km to miles):

SELECT dbo.ufn_km2mi (52.231)

ufn_km2mi

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

CREATE FUNCTION [dbo].[ufn_mi2km] (@mi decimal(10,3))
RETURNS decimal(10,3)
AS
BEGIN
RETURN (@mi * 1.60934)
END

To use this user-defined function (for example to convert 21.426 miles to km):

SELECT dbo.ufn_mi2km (21.426))

ufn_mi2km