Did you know that 1/2=0? If you didn’t you could be in for some interesting surprises.

1/2 returns 0 because 1 and 2 are both integers. When you divide two integers you get back an integer. Now, we, as human beings, know that what you probably wanted was to get back 0.5. Unfortunately, computers really aren’t all that smart and we have to be very explicit with them. So far all you’ve told it is that you want back the integer portion of 0.5, or 0. If you’ve been programming for a while you’ve run across this before and have very likely solved it by doing something like this:

DECLARE @IntA INT = 1, @IntB INT = 2;
SELECT 1.0*@IntA/@IntB ;

Because you are multiplying by a decimal (1.0) SQL Server is going to implicitly change the datatype to decimal. You could do something like this instead:

DECLARE @IntA INT = 1, @IntB INT = 2;
SELECT CAST(@IntA AS decimal (10,2)) / CAST(@IntB AS decimal (10,2)) ;

In this case you are telling SQL to do an explicit datatype conversion. SQL converts 1 and 2 to 1.00 and 2.00 (the CAST command) and then divides them. Since you specified that they are both decimals your output is also decimal and you get your 0.5.

FYI, this doesn’t just happen with math. A varchar containing a number could be converted to one of the numeric data types. A char could be converted to a varchar, etc. There are lots of different datatypes and some can be converted between each other, and some can’t. There is a graphic that tells you what can be converted and what can’t.

The only thing I’m not overly thrilled with about it is that where it says the little orange circle means Implicit conversion it should say Implicit OR explicit conversion. In other words if you can do an implicit conversion, you can do it explicitly too.

If you want to read more about Data type conversion here is a link to Microsoft’s online documentation on the subject.