Open In App

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

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 28 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads