Explicit and implicit datatype conversions

2

August 25, 2022 by Kenneth Fisher

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.

2 thoughts on “Explicit and implicit datatype conversions

  1. […] I talked about the difference between implicit and explicit datatype conversions. In it I ran this […]

Leave a Reply to Data Type Conversions in SQL Server – Curated SQL Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,739 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: