T-sql Cast & Convert

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。以下例子用于将文本字符串'123'转换为整型:

SELECT CAST('123' AS int) 

返回值是整型值123。如果试图将一个代表小数的字符串转换为整型值，又会出现什么情况呢？

SELECT CAST('123.4' AS int)  

CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。由于123.4不能用int数据类型来表示，所以对这个函数调用将产生一个错误:

Server: Msg 245, Level 16, State 1, Line 1  
Syntax error converting the varchar value
'123.4' to a column of data type int. 

要返回一个合法的数值，就必须使用能处理这个值的数据类型。对于这个例子，存在多个可用的数据类型。如果通过CAST()函数将这个值转换为decimal类型，需要首先定义decimal值的精度与小数位数。在本例中，精度与小数位数分别为9与2。精度是总的数字位数，包括小数点左边和右边位数的总和。而小数位数是小数点右边的位数。这表示本例能够支持的最大的整数值是9999999，而最小的小数是0.01。

SELECT CAST('123.4' AS decimal(9,2)) 

decimal数据类型在结果网格中将显示有效小数位:

123.40 

精度和小数位数的默认值分别是18与0。如果在decimal类型中不提供这两个值，SQL Server将截断数字的小数部分，而不会产生错误。

SELECT CAST('123.4' AS decimal) 

结果是一个整数值：

123 

在表的数据中转换数据类型是很简单的。下面的例子使用Product表，首先执行如下查询：

SELECT ProductNumber, ProductLine, ProductModelID   
FROM Production.Product  
WHERE ProductSubCategoryID < 4 

假定产品经理已经创建了一个系统，用于唯一地标识生产出来的每辆自行车，以便跟踪其型号、类型和类别。他决定合并产品号、产品生产线标识符、产品型号标识符和一个顺序号，为生产出来的每辆自行车创建一个唯一的序列号。在这个过程的第一步，他要求提供包括除顺序号之外的所有属性的所有可能产品的根标识符。

如果使用下面的表达式，就不能得到希望的结果，如图6-2所示。

SELECT ProductNumber   
       + '-'   
       + ProductLine   
       + '-'   
       + ProductModelID AS BikeSerialNum  
FROM Production.Product  
WHERE ProductSubCategoryID < 4 

我们没有得到希望的结果，而得到了有点奇怪的错误消息：请把nvarchar值转换为int。因为之前我们没有要求进行任何转换，所以这个错误很奇怪。这个查询的问题在于我们试图利用第一个连接符来连接字符值ProductNumber，利用第二个连接符连接另一个字符值ProductLine，最后连接的是ProductModelID字符值(它是一个整数)。

查询引擎会把连接符当成一个数学运算符，而不是一个字符。不管结果是什么，都需要更正这个表达式，以确保使用正确的数据类型。以下表达式执行了必要的类型转换，返回如图6-3所示的结果：

SELECT ProductNumber   
       + '-'   
       + ProductLine   
       + '-'   
       + CAST(ProductModelID AS char(4)) AS BikeSerialNum  
FROM Production.Product  
WHERE ProductSubCategoryID < 4 

如果把整型值转换为字符类型就不会增加多余的空格了。查询引擎将把这些值用加号和连接符组合在一起，进行字符串连接运算，而不是和前面的数值进行加法或者减法运算了。

 

对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。以下的两个例子和上一节的例子类似：

SELECT CONVERT(int, '123')  
SELECT CONVERT(decimal(9,2), '123.4') 

CONVERT()函数还具有一些改进的功能，它可以返回经过格式化的字符串值，且可以把日期值格式化成很多形式。有28种预定义的符合各种国际和特殊要求的日期与时间输出格式。表6-8列出了这些日期格式。

表  6-8

格式序号	年份位数	小时格式	说   明	示    例
0	2	12	默认	Apr 25 2005 1:05PM
1	2		美国	04/24/05
2	2		ANSI	05.04.25
3	2		英国/法国	25/04/05
4	2		德国	25.04.05
5	2		意大利	25-04-05
6	2		定制-仅日期	25 Apr 05
7	2		定制-仅日期	Apr 25，05
8		24	定制-仅时间	13:05:35
9	4	12	默认，毫秒	Apr 25 2005 1:05:35:123PM
10	2		美国	04-25-05
11	2		日本	05/04/25
12	2		ISO	050425

(续表)

格式 序号	年份 位数	小时格式	说    明	示    例
13	4	24	欧洲	25 Apr 2005 13:05:35:123
14		24	定制时间，毫秒	13:05:35:123
100	4	12	默认	Apr 25 20051:05PM
101	4		美国	04/24/05
102	4		ANSI	2005.04.25
103	4		英国/法国	25/04/2005
104	4		德国	25.04.2005
105	4		意大利	25-04-05
106	4		定制-仅日期	25Apr2005
107	4		定制-仅日期	Apr25，2005
108		24	定制-仅时间	13:05:35
109	4	12	默认，毫秒	Apr 252005 1:05:35:123PM
110	4		美国	04-25-2005
111	4		日本	2005/04/25
112	4		ISO	20050425
113	4	24	欧洲	25 Apr 2005 13:05:35:123
114		24	定制时间，毫秒	13:05:35:123

这个函数的第三个参数是可选的，该参数用于接收格式代码整型值。表中的例子用于对DateTime数据类型进行转换。在转换SmallDateTime数据类型时，格式不变，但一些元素会显示为0，因为该数据类型不支持毫秒。以下的脚本例子将输出格式化的日期：

SELECT 'Default Date:' + CONVERT(Varchar(50), GETDATE(), 100)  
Default Date: Apr 25 2005 1:05PM  
SELECT 'US Date:' + CONVERT(Varchar(50), GETDATE()，101)  
US Date: 04/25/2005  
SELECT 'ANSI Date:' + CONVERT(Varchar(50), GETDATE(), 103)  
ANSI Date: 2005.04.25  
SELECT 'UK/French Date:' +CONVERT (Varchar(50), GETDATE(), 103)   
UK/French Date: 25/04/2OO5  
SELECT 'German Date:' + CONVERT(Varchaz(50), GETDATE(), 104)   
German Date: 25.04.2005 

格式代码0，1和2也可用于数字类型，它们对小数与千位分隔符格式产生影响。而不同的数据类型所受的影响是不一样的。一般来说，使用格式代码0(或者不指定这个参数的值)，将返回该数据类型最惯用的格式。使用1或者2通常显示更为详细或者更精确的值。以下例子使用格式代码0：

DECLARE @Num Money  
SET @Num = 1234.56  
SELECT CONVERT(varchar(50), @Num, 0) 

返回结果如下:

1234.56 

使用值1则返回如下结果:

1,234.56 

使用值2则返回如下结果:

1234.5600 

以下例子和上例相同，但是使用Float类型:

DECLARE @Num float 
SET @Num = 1234.56  
SELECT CONVERT(varchar(50), @Num, 2) 

使用值0不会改变所提供的格式，但是使用值1或2将返回以科学计数法表示的数字，后者使用了15位小数：

1.23456000000000e+003