Casting value or an expression from one data type to another in SQL server

SQL Server uses the CAST() function to cast or convert a value or an expression from one data type to another.

Syntax :

CAST ( value AS targettype [ ( length ) ] )

Parameters used :

• value –
  value can be any value of any type that will be converted.
  
  
• targettype –
  targettype is the target data type to which the value will be converted. e.g. INT, BIT, SQL_VARIANT, etc.
• length –
  length is an optional parameter that specifies the length of the targettype, default length is 30.

For better understanding, let us run the below query as follows.

SELECT 3 + '3' AS Result;

Output :

Result
6

Its result is 6 as a number. In above statement, SQL Server implicitly converts the character string ‘3’ to the number 3.

Note –
When two values with different data types are used in SQL Server, it will convert the lower to higher data type before it process if further. This is known as implicit conversion.

On the other hand, SQL server could use explicit conversions where the CAST() function is used explicitly to convert a value of one type to another as follows.

SELECT 3 + CAST('3' AS INT) AS Result;

Output :

Result
6

To cast a value of the data types in different scenarios, SQL Server will return a truncated value or a rounded value based on the following rules as follows.

From Data Type	To Data Type	Behavior
numeric	numeric	Round
numeric	int	Truncate
float	int	Round
float	numeric	Round
float	datetime	Round
datetime	int	Round

Examples –

• Example-1 :
  Below example uses the CAST() function to convert the decimal type value to an integer type value.

  SELECT CAST(5.95 AS INT) AS Result;

  Output :

  Result
  5

• Example-2 :
  Below example uses the CAST() function to convert the decimal type value to another decimal type value number with the zero scale.

  SELECT CAST(5.95 AS DEC(3, 0)) Result;

  Output :

  Result
  6

• Example-3 :
  Using the CAST() function to convert a string to a datetime value example.
  The below example uses the CAST() function to convert the string to a datetime.

  SELECT CAST('2020-09-14' AS DATETIME) 
  AS Result;

  Output :

  Result
  2020-09-14 00:00:00.000