SQL CAST Function

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

CAST (Expression AS Data_Type [(Length)])

-- Cast in SQL example
SELECT CAST (123 AS VARCHAR(50)) AS [result_name]
FROM [Source]
  • Expression: Valid expression that you want to convert into the desired data type in SQL Server.
  • Data_Type: Specify the Data Type to which you want to convert an 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 Example 1

The SQL Server CAST Function converts any valid expression to the desired type. The following CAST function query converts string to integer and decimal.

-- SQL Server CAST FUNCTION Example 
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 Result;  

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

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

First, we declared a variable of the VARCHAR type. Next, we were assigning the string data ‘12121’.

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

We are converting the string value to an integer. We also assigned a new name, ‘Result’ using ALIAS column in SQL Server.

SELECT CAST(@str AS INT) AS Result;

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

SELECT CAST(123.456 AS INT) AS Result;

Converting the string value to DateTime and datetime2 data type.

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

Last, we used CAST to convert the date time to varchar

SELECT CAST(GETDATE() AS VARCHAR(50)) AS Result;

CAST Function Example 2

Here, we use the Sql Server Cast function to work with NULL values.

-- SQL Server CAST FUNCTION Example 
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 tried to convert the ‘Tutorial Gateway’ string to date time. It is not possible so, this cast function was returning Error as output.

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

Execute the above cast function query

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

CAST Function Example 3

In this example, we will apply the CAST function to our Employee table.

SQL CAST Function 4

CAST CODE

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

Comments are closed.