Login     Register

        Contact Us     Search

XLeratorDB/convert

Dec 27

Written by: Charles Flock
12/27/2008 5:35 PM  RssIcon

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

Tags:
Categories:
Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service