SQL SERVER – Data Type Conversions

In general, while writing Stored Procedures or Queries in the SQL Server, there will be a need to assign an object of one data type to another object of different data type. Also, Some times we have to concatenate a string to an integer or Truncate/Preserve the Decimal parts of a column or change the DATETIME formats. All these actions are possible by Data type Conversion.

In SQL Server, two types of data type conversion is possible.

Implicit Data Type Conversion:                                        This will be handled by SQL Server automatically where it converts the data from one data type to another. These type of conversions are invisible to the user. For example, if an INT is compared to BIGINT, then INT is implicitly converted to BIGINT before the comparison.

Example :

Let us first create a table  IMPLICIT_CONVERSION.

CREATE TABLE IMPLICIT_CONVERSION                             (ID INT,VALUE_INT  INT, VALUE_DECIMAL DECIMAL(5,2))

The table includes three columns to hold integer information: VALUE_INT , VALUE_DECIMAL. The two column names reflect the names of the data types used to define the columns. Now let's Insert data into those columns:

INSERT INTO IMPLICIT_CONVERSION
Select 1,555,555

Finally, Let us see how the data is stored in this table

Select * FROM IMPLICIT_CONVERSION

OutPut :

As you can see, each Integer value is a little different. The VALUE_INT Column stores the integer values while the VALUE_DECIMAL stores the Decimal value.

Explicit Data Type Conversion:                                            In Order to Convert the data explicitly, we have to use either the CAST() or CONVERT() Transact-SQL functions.

Examples :

Select CAST('5' As INT) Integer_Value                       Select CONVERT(INT,27.23) Integer_Value
Select CONVERT(VARCHAR(10),GetDate(),103) CurrentDate

Output :