Datatypes are very important concepts of SQL Server and there are quite often need to convert them from one datatypes to another datatype. I have seen that deveoper often get confused when they have to convert the datatype. There are two important concept when it is about datatype conversion.

Implicit Conversion: Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit Conversions: Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.

What it means is that if you are trying to convert value from datetime2 to time or from tinyint to int, SQL Server will automatically convert (implicit conversation) for you. However, if you are attempting to convert timestamp to smalldatetime or datetime to int you will need to explicitely convert them using either CAST or CONVERT function as well appropriate parameters.

Let us see a quick example of Implict Conversion and Explict Conversion.

Implicit Conversion:

Explicit Conversion:

You can see from above example that how we need both of the types of conversion in different situation. There are so many different datatypes and it is humanly impossible to know which datatype require implicit and which require explicit conversion. Additionally there are cases when the conversion is not possible as well.

Microsoft have published a chart where the grid displays various conversion possibilities as well a quick guide.

Download SQL Server Data Type Conversion Chart

Reference: Pinal Dave (http://blog.sqlauthority.com)