CAST and CONVERT (Transact-SQL)

By Code Collector

SQL Server sample code: CAST and CONVERT (Transact-SQL)

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

Syntax
for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))

-- Or, using CONVERT

SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5

FirstName      LastName         Title                  Sick Leave
-------
--      ---------      -------------------   -----------
Gustavo         Achong            Mr.                   *
Catherine      Abel                  Ms.                   *
Kim                Abercrombie    Ms.                   *
Humberto    
  Acevedo           Sr.                   *
Pilar                Ackerman         Sra.                  *

(5
row(s) affected)

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO
USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO
USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO
CONVERT(XML, '<root><child/></root>')
CONVERT(XML, '<root>          <child/>         </root>', 1)
CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)
SELECT
   GETDATE()
AS UnconvertedDateTime,
  
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
  
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO
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 ;
GO
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'
--The following example shows how Style 1 can force the result
--
to be truncated. The truncation is caused by
--including the characters 0x
in the result.
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'
--The following example shows that Style 2 does not truncate the
--
result because the characters 0x are not included in
--the
result.
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'
--Convert the character value 'Name' to a binary value.
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, character to binary'
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'
SELECT CONVERT(binary(4), '4E616D65', 1) AS 'Style 2, character to binary'
CAST and CONVERT (Transact-SQL)  (2774 Views)