XLeratorDB/convert
Dec
27
Written by:
Charles Flock
12/27/2008 5:35 PM
An introduction to the latest function package for SQL Server - Unit Conversions.
We are pleased to announce the availability of the XLeratorDB/convert suite of functions for SQL Server. Use these functions to convert from one unit of measure to another. This suite of functions consists of over 70 individual functions which can be used in any T-SQL statement, stored procedure, trigger, CTE, VIEW, or user-defined procedure or any other place you might use a SQL Server built-in function.
In developing this function, we adhered to the standards in NIST Special Publication 811 · 2008 Edition. In some cases, this means that our calculations are different than what EXCEL calculates and also may be different than what Google calculates.
The XLeratorDB/convert suite of functions is a superset of functions implemented in EXCEL as the CONVERT function. This table summarizes the XLratorDB/convert categories and the equivalent function in EXCEL:
|
XLeratorDB function
|
EXCEL group
|
|
CONVERTWEIGHT
|
Weight and mass
|
|
CONVERTLENGTH
|
distance
|
|
CONVERTTEMP
|
Temperature
|
|
CONVERTSPEED
|
(no comparable EXCEL function)
|
|
CONVERTAREA
|
(no comparable EXCEL function)
|
|
CONVERTVOLUME
|
liquid measure
|
In addition, XLeratorDB has lower level functions that can be used directly to perform a unit conversion. For example, if you want to convert to some length in inches, you can simply use the INCHES function. If you want to convert speed to kilometers per hour, you can call the KMHR function. For a complete list of functions, see the documentation.
XLeratorDB conforms to the specifications in NIST Special Publication 811 · 2008 Edition which can be found here http://physics.nist.gov/Pubs/SP811/. Unless otherwise noted, the XLeratorDB definition conforms to the NIST definition. For the units that are covered in EXCEL we discovered the following anomalies:
Length
|
Unit
|
EXCEL Definition
|
XLeratorDB Definition
|
|
PICA
|
1/72 of an inch
|
1/6 of an inch
|
|
YARD
|
3.000000001 feet
|
3 feet
|
|
YARD
|
36.00000001 inches
|
36 inches
|
|
YARD
|
0.9144000003 meters
|
.9144 meters
|
|
MILE
|
1759.99999942257 yards
|
1760 yards
|
|
METER
|
3.280839895013120000 feet
|
3.2808399 feet
|
|
METER
|
39.370078740157500000 inches
|
39.3700787 inches
|
|
METER
|
0.000621371192237334 miles
|
0.000621371 miles
|
|
METER
|
0.000539956803455724 nautical miles
|
0.000539957 nautical miles
|
|
NAUTICAL MILES
|
6076.1154855643 feet
|
6076.11549 feet
|
|
NAUTICAL MILES
|
72913.3858267717 inches
|
72913.3858 inches
|
|
NAUTICAL MILES
|
1.15077944802354 miles
|
1.15077945 miles
|
|
NAUTICAL MILES
|
2025.37182785694 yards
|
2025.37183 yards
|
The PICA definition in EXCEL is 1/12 of the definition in XLeratorDB in all cases.
Volume
|
Unit
|
Excel Definition
|
XLeratorDB Definition
|
|
CUP
|
0.236639716032341 liters
|
0.2365882 liters
|
|
CUP
|
0.416428088074541 imperial pints
|
0.41633692 imperial pints*
|
|
GALLON
|
3.78623545651745 liters
|
3.785412 liters
|
|
GALLON
|
6.66284940919265 imperial pints
|
6.66139072 imperial pints*
|
|
LITER
|
4.22583333333333 cups
|
4.22675284 cups
|
|
LITER
|
0.264114583333333 gallons
|
0.264172052 gallons
|
|
LITER
|
33.8066666666667 fluid ounces
|
33.8140227 fluid ounces
|
|
LITER
|
2.11291666666667 pints
|
2.11337642 pints
|
|
LITER
|
1.05645833333333 quarts
|
1.05668821 quarts
|
|
LITER
|
67.6133333333333 tablespoons
|
67.6280454 tablespoons
|
|
LITER
|
1.75975569552166 imperial pints
|
1.75975326 imperial pints
|
|
FLUID OUNCE
|
0.0295799645040426 liters
|
0.02957353 liters
|
|
FLUID OUNCE
|
0.0520535110093176 imperial pints
|
0.05204211 imperial pints*
|
|
PINT
|
0.473279432064682 liters
|
0.4731765 liters
|
|
PINT
|
0.832856176149081 imperial pints
|
0.83267384 imperial pints*
|
|
QUART
|
0.946558864129363 liters
|
0.9463529 liters
|
|
QUART
|
1.66571235229816 imperial pints
|
1.66534768 imperial pints*
|
|
TABLESPOON
|
0.0147899822520213 liters
|
0.01478676 liters
|
|
TABLESPOON
|
0.0260267555046588 imperial pints
|
0.0260210575 imperial pints*
|
|
IMPERIAL PINT
|
2.401375 cup
|
2.40190084 cups*
|
|
IMPERIAL PINT
|
0.1500859375 gallons
|
0.150118803 gallons*
|
|
IMPERIAL PINT
|
0.568260698087162 liters
|
0.56826125 liters
|
|
IMPERIAL PINT
|
19.211 fluid ounces
|
19.2152068 fluid ounces*
|
|
IMPERIAL PINT
|
1.2006875 pints
|
1.20095042 pints*
|
|
IMPERIAL PINT
|
0.60034375 quarts
|
0.600475211 quarts*
|
|
IMPERIAL PINT
|
38.422 tablespoons
|
38.4304135 tablespoons*
|
* Not covered in Publication 811
Weight
|
Unit
|
Excel Definition
|
XLeratorDB Definition
|
|
GRAMS
|
0.00220462291469134 lbs avdp
|
0.00220462262 lbs avdp
|
|
GRAMS
|
0.0352739718003627 oz advp
|
0.03527396919 oz advp
|
|
POUND AVOIRDUPOIS
|
453.592309748811 grams
|
453.59237 grams
|
|
POUND AVOIRDUPOIS
|
16.000002342941 oz avdp
|
16 oz avdp
|
|
OUNCE AVOIRDUPOIS
|
28.3495152079732 grams
|
28.34952 grams
|
|
OUNCE AVOIRDUPOIS
|
0.0624999908478882 lbs avdp
|
0.0625 lbs avdp
|
Temperature
There were no differences between the EXCEL calculations and XLeratorDB calculations.
Area
This is no direct conversion to area in EXCEL, but you can call the distance function twice to convert from some units to others. For example, you could convert 100 square feet to square meters you could enter CONVERT(CONVERT(100,”ft”,”m”), “ft”, ”m”) in EXCEL. But, if you wanted to convert acres to square feet or square miles to hectares, you would have to do some sort of multiplication. If we compare the EXCEL calculation to the results from XLeratorDB we find the following anomalies:
|
Unit
|
Excel Definition
|
XLeratorDB Definition
|
|
FT2
|
0.111111111038204 yd2
|
0.111111111111111 yd2
|
|
IN2
|
0.000771604937765302 yd2
|
0.000771604938271605 yd2
|
|
M2
|
10.7639104167097 ft2
|
10.7639104 ft2
|
|
M2
|
1550.0031000062 in2
|
1550.0031 in2
|
|
M2
|
1.19599004551631 yd2
|
1.19599005 yd2
|
|
MI2
|
2589988.110336 m2
|
2589998.11 m2
|
|
MI2
|
3097599.99796745 yd2
|
3097600 yd2
|
|
YD2
|
9.00000000590551 ft2
|
9 ft2
|
|
YD2
|
1296.00000085039 in2
|
1296 in2
|
|
YD2
|
0.83612736054864 m2
|
0.83612736 m2
|
Speed
Like area, there are no direct conversions to speed in EXCEL. The conversion of speeds requires the conversion of distance and time independently. For example, to convert 55 miles per hour to feet per second, you would have to convert 55 miles to feet and hours to seconds, in separate cells, and then concatenate the results. We have already listed the anomalies in the EXCEL distance calculations. The EXCEL conversion of the units of time (hours, minutes, and seconds) is accurate.
How we tested
We created millions of randomly generated rows of test data for each function and compared the results to what EXCEL calculated and what Google calculated. The anomalies described above are a result of that testing. We believe that we have achieved accuracy in all the functions to be consistent with the NIST.
A final thought
We hope that you find these functions as useful as we do. It eliminates the need to go and look up the conversion factor (usually on the Internet) and then have to build the calculation into whatever you are doing. And, in looking up the calculation, you can never be sure if your source has the right precision and significance. It also really surprised us that so many EXCEL calculations did not adhere to standards on significance and precision. For large quantities of data, this is a fast, cheap, easy solution. Let us know what you think, by sending us an email to support@westclintech.com