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 (https://blog.sqlauthority.com)
6 Comments. Leave new
Great example.
This chart really useful…….Thank u Pinal
Thanks for sharing!!
It is really helpfull. Thanks Pinal.
A side effect of data type conversion (implicit or explicit) is that it can have negative impact on performance. Assume you get an implicit conversion of the modifieddate in the example above, that will cause an index scan instead of an index seek (if there is an index on modifieddate column).
If you have different datatypes in joins between two tables, you’ll suffer a lot if the implicit conversion will occur on the largest table.
Personally I would prefer if you could turn of implicit conversion (at server, database, connection or statement level) to avoid any unwanted conversions. If you need conversion, you should always have to specify it and it would force you to think of the side effect.
Thanks sir i am always following ur blog.if we have CONVERT why we r using CAST is der any difference ?