SQL CAST Function

The SQL Server CAST Function converts an expression from one data type to another. If it is unable to convert an expression into the desired data type, this function will return an Error. The syntax of the SQL CAST Function is

CAST(Expression AS Data_Type [(Length)])

SELECT CAST(123 AS VARCHAR(50)) AS [result_name]
FROM [Source]
  • Expression: A Valid expression you want to convert into the desired data type.
  • Data_Type: Specify the Data Type to which you want to convert the given expression
  • Length: This is an optional parameter of integer type. Used to define the length of the target data type. By default, it is 30.

SQL CAST Function Example

This function converts any valid expression to the desired type. The following query converts a string to an integer and decimal.

First, we declared a variable of the VARCHAR. Next, we were assigned the string data ‘12121’. Next, we convert the string value to an integer. We also assigned a new name, ‘Result’, using THE ALIAS column in SQL Server.

In the next line, We used the SQL CAST function directly on the decimal value 123.456 and converted it to an integer (from higher to lower). Here, it will truncate the decimal values and return 123. I suggest you refer to Data Types to know the data types and their limitations.

The last but one statement changes the string value to DateTime and datetime2 datatype.

Last, we used it to change the date time to varchar.

DECLARE @str AS VARCHAR(50)
SET @str = '12121'

SELECT CAST(@str AS INT) AS Result; 

-- Direct Inputs
SELECT CAST('1234' AS DECIMAL(10, 2)) AS Result; 

SELECT CAST(123.456 AS INT) AS Result; 

SELECT CAST('07/03/2017' AS DATETIME) AS Value;  

SELECT CAST('07/03/2017' AS DATETIME2) AS Value;

SELECT CAST(GETDATE() AS VARCHAR(50)) AS Value;
SQL CAST Function 1

CAST Example 2

In this example, we use the SQL Server Cast function to work with NULL values as integers.

DECLARE @str AS VARCHAR(50)
SET @str = NULL

SELECT CAST(@str AS INT) AS Result;

SELECT CAST(NULL AS INT) AS Result;
SQL CAST Function 2

Let us use what happens if we try to convert the ‘Tutorial Gateway’ string to date time. It is not possible, so this was returning an Error as an output.

SELECT CAST('Tutorial Gateway' AS INT) AS Result;

Execute the above query and see the error message.

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the Varchar value 'Tutorial Gateway' to data type int.

We will apply the SQL Server CAST function to our Employee table in this example.

Employee Records

CODE

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,CAST([YearlyIncome] AS DECIMAL(10, 3)) AS [Income]
      ,CAST([Sales] AS INT) AS [Amount]
      ,CAST([HireDate] AS DATETIME2) AS [Date]
  FROM [Employee]
SQL Server CAST Function Example 5
Categories SQL

Comments are closed.