SQL CAST Function

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

CAST(Expression AS Data_Type [(Length)])

SELECT CAST(123 AS VARCHAR(50)) AS [result_name]
FROM [Source]
  • Expression: Valid expression that 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 datatype. By default, it is 30.

SQL CAST Example

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

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

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

The last but one statement changing 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

Example 2

Here, we use the SQL Server Cast function to work with NULL values as integer’s.

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 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 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 CAST Function 5

Comments are closed.