MS SQL Server – Type Conversion

There are various databases that have huge collections of data, and we know that the data is arranged in an organized manner. The data present in the database might be of numeric or alphabet type while a few might be alphanumeric (a combination of alphabets and numbers) and others might be text, image, etc. The data that has to be sorted in the category/type where it actually belongs is called data types. The concept of data types helps us sort the data in its category. 

Example: Consider a schema given below as follows.

Creating Table :

create table student
(
rollnumber int, 
name varchar2(30), 
marks int
);

The above schema has a roll number as the first attribute. It is assigned as Integer as the roll number generally numbers arrangement in order. The same goes for marks as well. The name is assigned as varchar with a maximum count of 20 characters. It can accommodate characters and numbers as well.

Inserting Data: A value is being inserted in the table as follows.

insert into student values('120' ,'Bam' , '2T');

The value will not be inserted into the table due to an error. The marks attribute belongs to integer yet there was a character inserted along with a number making an error. 

Type Conversion: The process of converting from one data type to the other is known as type conversion or typecasting. There are two kinds of typecasting as follows.

• Implicit conversion: The conversion in which one datatype can be converted to another datatype automatically is called an implicit conversion.
• Explicit conversion: The conversion in which the data type has to be converted manually is called an explicit conversion. 

The explicit conversion has to be done in SQL Server using Cast or Convert function. The syntax is as follows.

cast(old _datatype as new_datatypename);
convert(old_datatype as new_datatypename);

Any one of the above functions can be used for explicit conversion. A cast function can be used instead of convert if it can adhere to the ISO(International Organization for Standardization) and it can be used as vice versa if we take the advantage of style functionally.

Example:

cast(150 as decimal2,2));

150 is of integer type and it has to be converted as a float with precision as 2 and scale as 2.