Skip to content
Related Articles

Related Articles

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

Improve Article
Save Article
  • Last Updated : 28 Dec, 2020
Improve Article
Save Article

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 TypeBehavior
numericnumericRound
numericintTruncate
floatintRound
floatnumericRound
floatdatetimeRound
datetimeintRound

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
My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!