Bing Yee程序人生

USE AdventureWorks2008R2;
GO
--CAST

Select 'CAST' "CAST",
CAST('1990-11-1' AS VARCHAR(10)) 转为字符串,
CAST('1990-11-1' AS DATETIME) 转为日期

--CONVERT
Select 'CONVERT' "CONVERT",
CONVERT(VARCHAR(10),'2999') 转为字符串,
CONVERT(INT,'2999') 转为整数

--同时使用 CAST 和 CONVERT

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

-- Use CONVERT.
Select SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
Where CONVERT(int, ListPrice) LIKE '3%';
GO

--B. 将 CAST 与算术运算符结合使用

Select CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS Computed
FROM Sales.SalesPerson
Where CommissionPCT != 0;
GO
/*
C. 使用 CAST 进行连接
以下示例使用 CAST 连接非字符型非二进制表达式。
*/
Select 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
Where ListPrice BETWEEN 350.00 AND 400.00;
GO

/*
D. 使用 CAST 生成可读性更高的文本
以下示例使用选择列表中的 CAST 将 Name 列转换为 char(10) 列。
*/

Select DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail AS s
JOIN Production.Product AS p
    ON s.ProductID = p.ProductID
Where Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Select DISTINCT Name, s.UnitPrice
FROM Sales.SalesOrderDetail AS s
JOIN Production.Product AS p
    ON s.ProductID = p.ProductID
Where Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

/*
E. 将 CAST 与 LIKE 子句一起作用
以下示例将 money 列 SalesYTD 转换为 int 列，然后再转换为 char(20) 列，以便在 LIKE 子句中使用该列。
*/
Select p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person AS p
JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
Where CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

/*
G. 对 datetime 数据使用 CAST 和 CONVERT
以下示例显示当前日期和时间，使用 CAST 将当前日期和时间更改为字符数据类型，
然后使用 CONVERT 以 ISO 8901 格式显示日期和时间。
*/
Select
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

/*
以下示例与上述示例几乎完全相反。 该示例将日期和时间显示为字符数据，使用 CAST 将字符数据更改为 datetime 数据类型，然后使用 CONVERT 将字符数据更改为 datetime 数据类型。
*/

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

/*
H. 使用 CONVERT 处理二进制和字符数据
以下示例使用不同样式显示转换二进制和字符数据的结果。
*/

--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 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), 'JackChen', 0) AS [Style 0, character to binary];

Select CONVERT(binary(4), '0x4E616D65', 1) AS [Style 1, character to binary];

/*
I. 转换日期和时间数据类型
以下示例演示了日期、时间及 datetime 数据类型的转换。
*/

DECLARE @d1 date, @t1 time, @dt1 datetime;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
select @d1 date格式,@t1 time格式,@dt1 datetim格式
-- 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];