## To be precise. . .

Oct 6

Written by: Charles Flock
10/6/2008 10:36 AM

A brief journey into the world of significance and precision in T-SQL.

As we were testing the FLOOR function, we discovered that the following T-SQL statement:
Select wct .floor(2/3,.1)
did not return the expected result. After a little investigation, we discovered that simply entering the following statement:
SELECT 2/3
returned 0.
So, we started looking at the T-SQL documentation and found this http://msdn.microsoft.com/en-us/library/ms190476.aspx which was quite useful. It led us to believe that if we entered 2.0/3.0 we would get the resut that we were looking for:
SELECT 2.0/3.0
which gives us 0.666666, which is somewhat disconcerting as we were expecting 0.666667.

By entering 2/3, it appears that the engine conveted both the numerator and denominator to int data types, returning an int data type, which under the scale and precision rules maintainend the integrity of the value on the left hand side of the decimal (zero in this case).

We then decided to do a more structured test, the results of which are in the table below:

 SELECT STATEMENT RESULT SELECT cast(2 as smallint)/cast(3 as smallint) 0 SELECT cast(2 as tinyint)/cast(3 as tinyint) 0 SELECT cast(2 as int)/cast(3 as int) 0 SELECT cast(2 as bigint)/cast(3 as bigint) 0 SELECT cast(2 as decimal)/cast(3 as decimal) 0.6666666666666666666 SELECT cast(2 as float)/cast(3 as float) 0.666666666666667 SELECT cast(2 as numeric)/cast(3 as numeric) 0.6666666666666666666 SELECT cast(2 as money)/cast(3 as money) 0.6666 SELECT cast(2 as smallmoney)/cast(3 as smallmoney) 0.6666 SELECT cast(2 as real)/cast(3 as real) 0.6666667

It was only when our values were defined as float or as real that we got the results that we expected.

Tags:
Categories:

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < March 2023 >
SunMonTueWedThuFriSat
2627281234
567891011
12131415161718
19202122232425
2627282930311
2345678
Monthly
Go