SQL Authority News – Download SQL Server Data Type Conversion Chart

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.

Solarwinds

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

Implicit Conversion:

SQL Authority News - Download SQL Server Data Type Conversion Chart conversion1

Explicit Conversion:

SQL Authority News - Download SQL Server Data Type Conversion Chart conversion2

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)

Solarwinds
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #006
Next Post
SQL SERVER – Asynchronous Update and Timestamp – Check if Row Values are Changed Since Last Retrieve

Related Posts

6 Comments. Leave new

  • Great example.

    Reply
  • This chart really useful…….Thank u Pinal

    Reply
  • Thanks for sharing!!

    Reply
  • It is really helpfull. Thanks Pinal.

    Reply
  • Hakan Winther (@h_winther)
    February 24, 2013 5:28 pm

    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.

    Reply
  • Thanks sir i am always following ur blog.if we have CONVERT why we r using CAST is der any difference ?

    Reply

Leave a Reply

Menu