SQL Cast And Convert Statement

Cast statement

The Cast statement is used to convert a data type from one data type to another data type. If the conversion fails, the function will return an error. Otherwise, it will return the converted value. The Cast statement provides a data type to a dynamic parameter (?) or a NULL value in  SQL Server.

Syntax

CAST(expression AS datatype(length))   

Convert statement

The Convert statement is used to convert expressions from one type to another, In many cases there will be a need within a stored procedure or other routine to convert data from a DATETIME type to a VARCHAR type. The Convert statement is used for such things. The Convert() function can be used to display date/time data in various formats in SQL Server datatype. 

Syntax  

CONVERT(data_type(length), expression, style)   

Using CAST function

The CAST function in SQL Server is used to convert one data type to another. It is used to explicitly convert a value from one data type to another. This function takes two arguments: the expression to be converted and the target data type. In the below example, we retrieve the name of the OrderName for those OrderDetails that have a 1 as the first digit of OrderName, and converts their OrderId values to int. 

-- Use CAST
SELECT SUBSTRING(OrderName,1, 40) AS OrderName, OrderAddress
 FROM OrderDetails
 WHERE CAST( OrderId AS int)LIKE '2%';

The above SQL SELECT statement select the data from “OrderDetails” table and filters the results based on the “OrderId” column. This statement first converts the “OrderId” column to an INT data type using the CAST function, and then uses the LIKE operator to filter the results where the “OrderId” starts with '10'. This SELECT statement uses the SUBSTRING function to truncate the “OrderName” column to the first 40 characters. The second column selected is “orderAddress”.

The query will return rows from the OrderDetails table where OrderId starts with '10' and the OrderName will be truncated to 40 characters, along with orderAddress column of the same rows.

Output

Using CONVERT function

The CONVERT function in SQL Server is used to convert one data type to another. It is used for both explicit and implicit conversions. This function takes two arguments: the expression to be converted and the target data type.

In the below example, we retrieve the name of the OrderName for those OrderDetails that have a 1 as the first digit of OrderName, and converts their OrderId values to int.  

 SELECT SUBSTRING(OrderName, 1, 20) AS OrderName ,OrderAddress      
 FROM OrderDetails    
 WHERE CONVERT(int, OrderId) LIKE '1%';  

This SQL query retrieves data from the "OrderDetails" table, and filters the results based on the "OrderId" column. The query first converts the "OrderId" column to an INT data type using the CONVERT function, and then uses the LIKE operator to filter the results where the "OrderId" starts with '8'. This SELECT statement uses the SUBSTRING function to truncate the “OrderName” column to the first 40 characters. The second column selected is “OrderAddress”.

This SQL statement will return rows from the OrderDetail table where OrderId starts with '8' and the OrderName will be truncated to 40 characters, along with orderAddress column of the same rows.

Output

The CAST statement with arithmetic operators

 

In this example, we can calculate a single column computation (Computed) by dividing the total OrderId by the OrderDetails. This value is rounded to the nearest whole number and is then CAST to an int data type. 

-- Use ROUND  
SELECT CAST(ROUND(OrderId, 0) AS int) AS Computed      
FROM OrderDetails       
WHERE OrderId != 0;      

The above SQL SELECT statement select the data from “OrderDetail” table and filters the results based on the “OrderId” column. This statement first converts to ROUND the “OrderId” column to Computed Column using the CAST function, and then uses the WHERE codition to filter the results where the “OrderId” =0. This SELECT statement uses the  function to truncate the “OrderId” column to the . The second column selected is “0”.The query will return rows from the OrderDetails table.

Output

Using the Cast to concatenate statement 

This example concatenates noncharacter expressions by using Cast. It uses the Sample database.

SELECT 'The Order Details ' + CAST(OrderName AS varchar(12)) AS OrderCity       
FROM OrderDetails      
WHERE OrderId  BETWEEN 8 AND 10;  

The above SQL SELECT statement select the data from “OrderDetail” table and filters the results based on the 'OrderId” . This statement is select the “OrderName” column to an 'Varchar' data type using the CAST function, and then uses the to filter the result as OrderCity from OrderDetails table and Where OrderId between 8 and 10.

Output

We can see that in the above query we are select the Order details to cast to add column  OrderCity.

Using CAST to produce more readable text statement

This example uses Cast in the Select list, to convert the OrderName column to a char(50) column. 

SELECT DISTINCT CAST(OrderName AS char(50)) AS Name       
FROM OrderDetails      
WHERE OrderName LIKE 'Apple';  

 This SQL query Select  data from the "OrderDetail" table, and filters the results based on the "OrderName" column. The query first DISTINCT the "OrderName" column to an char data type using the DISTINCT function and as a column 'Name', and then uses the LIKE operator to filter the results where the "OrderName" starts with 'Apple'. 

Output  

We can see that in the above query we are select the Distinct Cast for OrderName to convert name.

Using Cast with the Like clause statement

This example converts the EmpName,EmpAddress,EmpCity column OrderId values to data type Int  and then to data type char(200) so that the Like clause can use it. 

SELECT EmpName ,EmpAddress, EmpCity       
FROM EmployeeDetail AS p       
JOIN OrderDetails AS s       
    ON s.OrderId = s.OrderId     
WHERE CAST(CAST(s.OrderId AS int) AS char(200)) LIKE '1%';    

This SQL query retrieves data from the "OrderDetail" table, and Add  the second table EmployeeDetail Join the "OrderId" column. The query first converts the "OrderId" column to an INT data type using the CONVERT function, and then uses the LIKE operator to filter the results where the "OrderId" starts with '1%'. This SELECT statement uses the JOIN function to truncate the “OrderId” column to the first 200 characters. 

Output

Using Convert or Cast with typed Xml statement

These examples show use of Convert to convert data to typed XML by using the Xml data type and columns SQL Server.

This statement  converts a string with white space text and markup into typed XML and removes all insignificant white space (boundary white space between nodes).

SELECT CONVERT(XML, '<root><child/></root>')    

 The above SQL SELECT statement select and Convert to XML and second 'WHITE SPACE' to namespace XML and root and child. 

Output

We can see that in the above query remove white space xml and root section.

This example converts a similar string with white space, text and markup into typed Xml and preserves insignificant white space boundary white space between nodes in Convert statement.

SELECT CONVERT(XML, '<root> <child/>  </root>', 1)   

 The above SQL SELECT statement select and Convert to XML space root. and second  child' to namespace XML and root and child. 

Output

This example casts a string with white space, text, and markup into typed Xml in sql  

 

OutPut

Using Cast and Convert with datetime data statement

The cast and convert Starting with Getdate() values, this example displays the current date and time, uses Cast to change the current date and time to a character data Type and then uses Convert to display the date and time in the ISO 8601 format in SQL

SELECT       
   GETDATE() AS UnconvertedDateTime,      
   CAST(GETDATE() AS nvarchar(40)) AS UsingCast,      
   CONVERT(nvarchar(40), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;      
GO  

The above SQL SELECT statement select the data from "GETDATE"  Function and  filters the results based on the 'UnconvertedDateTime'  This statement to  'GETDATE' function and to  “Nvarchar”  to UsingCast Column using the CAST function, and then uses the convert  to nvarchar to 'GETDATE' function  as usingConvertTo_ISO8601. .

OutPut

This statement  is approximately the opposite of the previous statement.

 

This example displays a date and time as character data, uses Cast to change the character data to the datetime data type and then uses Convert to change the character data to the datetime data type.

SELECT       
   '2006-04-25T15:50:59.997' AS UnconvertedText,      
   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,      
   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;   

The above SQL SELECT statement select the data from "GETDATE"  Function and  filters the results based on the 'UnconvertedDateTime'  This statement converts to 'GETDATE' function the “Nvarchar”  to UsingCast Column using the CAST function, and then uses the convert  to nvarchar to 'GETDATE' function  as usingConvertTo_ISO8601. .

Using Convert  with binary and character data statement

This example shows the results of binary and character data conversion, using different styles

SELECT CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];      

OutPut 

This example shows that style 1 can force a truncation result.  The characters 0x in the result set force the truncation. 

SELECT CONVERT(char(8), 0x4E616d65, 1) AS [Style 1 binary to character]   

OutPut

Converting date and time data types

This example shows the conversion of date, time, and datetime data types

DECLARE @d1 date, @t1 time, @dt1 datetime;      
SET @d1 = GETDATE();      
SET @t1 = GETDATE();      
SET @dt1 = GETDATE();      
SET @d1 = GETDATE();      
-- When converting date to datetime the minutes portion becomes zero.      
SELECT @d1 AS [date], CAST (@d1 AS datetime) AS [date as datetime];      
-- When converting time to datetime the date portion becomes zero       
-- which converts to January 1, 1900.      
SELECT @t1 AS [time], CAST (@t1 AS datetime) AS [time as datetime];      
-- When converting datetime to date or time non-applicable portion is dropped.      
SELECT @dt1 AS [datetime], CAST (@dt1 AS date) AS [datetime as date],     
   CAST (@dt1 AS time) AS [datetime as time];   

The above SQL statement declared to some parameters to Declare the 'date and time' and filters the results based on the “GETDATE” column.This statement first converting time to datetime the date portion become zero which converts to january1, 1990.“ this statement  to select and converting to 'CAST' and date to datetime. this statement in Convert to CAST to datetime to time.  

OutPut 

Using convert with datetime data in different formats

Starting with Getdate() values, this example uses Convert to display of all the date and time styles in section Date and Time styles of this article.  

Effects of data type precedence in allowed conversions statement. 

The above SQL statement is convert to parameters to DECLARE to @string varchar and filters the results based set string name. this statement is Select the 

DECLARE @string varchar(10);    
SET @string = 'R';    
SELECT @string + ' is a string.' AS Result  

The above SQL select  statement to Declare the 'String' and filters the results based on the “string ” name.This statement is result base to set string name is 'R'. select statement is result base to string name is 'is a string ' to string.   

OutPut

This example shows a similar query using an int variable instead.

DECLARE @notastring int;    
SET @notastring = '1';    
SELECT @notastring + ' is not a string.' AS Result   

In this case the Select statement will throw the following error.

OutPut

This statement In order to evaluate the expression @notastring + is not a string SQL Server statement and needs to follow the rules of data type precedence to complete the implicit conversion before the result of the expression can be calculated.

Because int has a higher precedence than varchar SQL Server attempts to convert the string to an integer and fails because this string cannot be converted to an integer.

 

If we provide a string that can be converted this statement will succeed, as seen in the following example.

DECLARE @notastring int;    
SET @notastring = '1';    
SELECT @notastring + '1   

In this case, the string '1' can be converted to the integer value 1 so this Select statement will return the value 2. When the data types provided are integers, the + operator becomes addition mathematical operator, rather than a string concatenation in SQL statement.

OutPut  

Summary

In this article, we learned how to use a SQL Cast and convert statement with various options.