Skip to content
Related Articles

Related Articles

Improve Article

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

  • Last Updated : 28 Dec, 2020

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

Syntax :

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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
Recommended Articles
Page :