SQL Cast And Convert Statement

Cast statement

The Cast statement  is used to convert a data type variable or data 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 .
    1. 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 datatype. 

      1. CONVERT(data_type(length), expression, style)   

      Using both Cast and Convert function

      These examples 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.  
      1. -- Use CAST      
      2.  USE sample;      
      3.  GO      
      4.  SELECT SUBSTRING(OrderName, 1, 40) AS OrderName, orderAddress      
      5.  FROM OrderDetails      
      6.  WHERE CAST( OrderId AS intLIKE  '10%';      
      7.  GO      
      9.  -- Use CONVERT.      
      10.  USE sample;      
      11.  GO      
      12.  SELECT SUBSTRING(OrderName, 1, 40) AS OrderName , orderAddress      
      13.  FROM OrderDetails     
      14.  WHERE CONVERT(int, OrderId) LIKE '8%';      
      15.  GO   

      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. 
      1. USE sample ;      
      2. GO      
      3. SELECT CAST(ROUND(OrderId, 0) AS intAS Computed      
      4. FROM OrderDetails       
      5. WHERE OrderId != 0;      
      6. GO    

      Using the Cast to concatenate statement 

      This example concatenates noncharacter expressions by using Cast. It uses the Sample  database.
      1. SELECT 'The Order Details ' + CAST(OrderName AS varchar(12)) AS OrderCity       
      2. FROM OrderDetails      
      3. WHERE OrderId  BETWEEN 8 AND 10;     

        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. 
        1. SELECT DISTINCT CAST(OrderName AS char(50)) AS Name       
        2. FROM OrderDetails      
        3. WHERE OrderName LIKE 'Mango';     

          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. 
          1. USE sample;      
          2. GO      
          3. SELECT EmpName ,EmpAddress, EmpCity       
          4. FROM EmployeeDetail AS p       
          5. JOIN OrderDetails AS s       
          6.     ON s.OrderId = s.OrderId     
          7. WHERE CAST(CAST(s.OrderId AS intAS char(200)) LIKE '1%';      
          8. GO    

            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).
            1. SELECT CONVERT(XML, '<root><child/></root>')    

            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.
            1. SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)    
            This example casts a string with white space, text, and markup into typed Xml
            This example casts a string with white space, text, and markup into typed Xml in sql  

            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
            1. SELECT       
            2.    GETDATE() AS UnconvertedDateTime,      
            3.    CAST(GETDATE() AS nvarchar(40)) AS UsingCast,      
            4.    CONVERT(nvarchar(40), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;      
            5. GO  
            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. 
            1. SELECT       
            2.    '2006-04-25T15:50:59.997' AS UnconvertedText,      
            3.    CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,      
            4.    CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;      
            5. GO     

              Using Convert  with binary and character data statement

              This example shows the results of binary and character data conversion, using different styles. 
              1. SELECT CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];      
              This example shows that style 1 can force a truncation result.  The characters 0x in the result set force the truncation. 
              1. SELECT CONVERT(char(8), 0x4E616d65, 1) AS [Style 1 binary to character]   

              Converting date and time data types

              This example shows the conversion of date, time, and datetime data types. ]]
              1. DECLARE @d1 date, @t1 time, @dt1 datetime;      
              2. SET @d1 = GETDATE();      
              3. SET @t1 = GETDATE();      
              4. SET @dt1 = GETDATE();      
              5. SET @d1 = GETDATE();      
              6. -- When converting date to datetime the minutes portion becomes zero.      
              7. SELECT @d1 AS [date], CAST (@d1 AS datetime) AS [date as datetime];      
              8. -- When converting time to datetime the date portion becomes zero       
              9. -- which converts to January 1, 1900.      
              10. SELECT @t1 AS [time], CAST (@t1 AS datetime) AS [time as datetime];      
              11. -- When converting datetime to date or time non-applicable portion is dropped.      
              12. SELECT @dt1 AS [datetime], CAST (@dt1 AS dateAS [datetime as date],     
              13.    CAST (@dt1 AS timeAS [datetime as time];   

              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 following example defines a variable of type Varchar, assigns an integer value to the variable, then selects a concatenation of the variable with a string. 
              1. DECLARE @string varchar(10);    
              2. SET @string = 1;    
              3. SELECT @string + ' is a string.' AS Result   
              The int value of 1 was converted to a varchar.
              This example shows a similar query using an int variable instead.
              1. DECLARE @notastring int;    
              2. SET @notastring = '1';    
              3. SELECT @notastring + ' is not a string.' AS Result   
                In this case the Select statement will throw the following error.
                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.
                1. DECLARE @notastring int;    
                2. SET @notastring = '1';    
                3. 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.


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