数据类型转换.docx
《数据类型转换.docx》由会员分享，可在线阅读，更多相关《数据类型转换.docx（14页珍藏版）》请在冰豆网上搜索。
数据类型转换
将一种数据类型的表达式显式转换为另一种数据类型的表达式。
CAST和CONVERT提供相似的功能。
Transact-SQL语法约定
语法
SyntaxforCAST:
CAST（expressionASdata_type[（length）]）SyntaxforCONVERT:
CONVERT（data_type[（length）],expression[,style]）
参数
expression
任何有效的表达式。
data_type
作为目标的系统提供数据类型。
这包括xml、bigint和sql_variant。
不能使用别名数据类型。
有关可用数据类型的详细信息，请参阅数据类型（Transact-SQL）。
length
nchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数。
对于CONVERT，如果未指定length，则默认为30个字符。
style
数据格式的样式，用于将datetime或smalldatetime数据转换成字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型），或将已知日期或时间格式的字符数据转换成datetime或smalldatetime数据；或者是字符串格式，用于将float、real、money或smallmoney数据转换成字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）。
如果style为NULL，则返回的结果也为NULL。
注意：
SQLServer支持本主题中列出的样式与CONVERT目标数据类型的所述组合。
不支持所有其他样式和组合。
请不要使用任何不支持的样式。
如果使用不支持的样式或不支持的样式与目标数据类型的组合，则可能会返回错误或不可靠的结果。
所有版本的SQLServer都不能保证这些结果的准确性。
SQLServer通过使用科威特算法来支持阿拉伯样式的日期格式。
在下表中，左侧的两列表示将datetime或smalldatetime数据转换为字符数据的style值。
将style值加100，可获得包括世纪数位的四位年份（yyyy）。
不带世纪数位（yy）
（1）
带世纪数位（yyyy）
标准
输入/输出（3）
-
0或100（1,2）
默认设置
monddyyyyhh:
miAM（或PM）
1
101
美国
mm/dd/yyyy
2
102
ANSI
yy.mm.dd
3
103
英国/法国
dd/mm/yy
4
104
德国
dd.mm.yy
5
105
意大利
dd-mm-yy
6
106
（1）
-
ddmonyy
7
107
（1）
-
mondd,yy
8
108
-
hh:
mi:
ss
-
9或109（1,2）
默认设置+毫秒
monddyyyyhh:
mi:
ss:
mmmAM（或PM）
10
110
美国
mm-dd-yy
11
111
日本
yy/mm/dd
12
112
ISO
yymmdd
-
13或113（1,2）
欧洲默认设置+毫秒
ddmonyyyyhh:
mi:
ss:
mmm（24h）
14
114
-
hh:
mi:
ss:
mmm（24h）
-
20或120
（2）
ODBC规范
yyyy-mm-ddhh:
mi:
ss（24h）
-
21或121
（2）
ODBC规范（带毫秒）
yyyy-mm-ddhh:
mi:
ss.mmm（24h）
-
126（4）
ISO8601
yyyy-mm-ddThh:
mi:
ss.mmm（无空格）
127（6,7）
带时区Z的ISO8601。
yyyy-mm-ddThh:
mi:
ss.mmmZ
（无空格）
-
130（1,2）
回历（5）
ddmonyyyyhh:
mi:
ss:
mmmAM
-
131
（2）
回历（5）
dd/mm/yyhh:
mi:
ss:
mmmAM
1这些样式值将返回不确定的结果。
包括所有（yy）（不带世纪数位）样式和一部分（yyyy）（带世纪数位）样式。
2默认值（style0或100、9或109、13或113、20或120以及21或121）始终返回世纪数位（yyyy）。
3转换为datetime时输入；转换为字符数据时输出。
4为用于XML而设计。
对于从datetime或smalldatetime到字符数据的转换，其输出格式如上一个表所述。
5回历是有多种变体的日历系统。
SQLServer2005使用科威特算法。
重要提示：
默认情况下，SQLServer基于截止年份2049年来解释两位数的年份。
换言之，就是将两位数的年份49解释为2049，将两位数的年份50解释为1950。
许多客户端应用程序（如基于自动化对象的应用程序）都使用截止年份2030年。
SQLServer提供了“两位数年份截止”配置选项，可通过此选项更改SQLServer使用的截止年份，从而对日期进行一致处理。
建议您指定四位数年份。
6仅支持从字符数据转换为datetime或smalldatetime。
仅表示日期或时间成分的字符数据转换为datetime或smalldatetime数据类型时，未指定的时间成分设置为00:
00:
00.000，未指定的日期成分设置为1900-01-01。
7使用可选的时间区域指示符（Z）更便于将具有时区信息的XMLdatetime值映射到没有时区的SQLServerdatetime值。
Z是时区UTC-0的指示符。
其他时区则以+或-方向的HH:
MM偏移量来指示。
例如：
2006-12-12T23:
45:
12-08:
00。
从smalldatetime转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。
使用相应的char或varchar数据类型长度从datetime或smalldatetime值转换时，可截断不需要的日期部分。
下表显示可用来将float或real转换为字符数据的style值。
值
输出
0（默认值）
最多包含6位。
根据需要使用科学记数法。
1
始终为8位值。
始终使用科学记数法。
2
始终为16位值。
始终使用科学记数法。
注意：
如果为float和real转换指定样式126，则输出分别等同于style值为2和1。
下表显示可用来将money或smallmoney转换为字符数据的style值。
值
输出
0（默认值）
小数点左侧每三位数字之间不以逗号分隔，小数点右侧取两位数，例如4235.98。
1
小数点左侧每三位数字之间以逗号分隔，小数点右侧取两位数，例如3,510.92。
2
小数点左侧每三位数字之间不以逗号分隔，小数点右侧取四位数，例如4235.9819。
注意：
如果为从money或smallmoney到字符数据的转换指定样式126，则输出等同于style值为2。
下表显示可用来将字符串输入转换为xml数据的style值。
值
输出
0（默认值）
使用默认的分析行为，即放弃无用的空格，且不允许使用内部DTD子集。
注意：
转换为xml数据类型时，SQLServer2005的无用空格处理方式不同于XML1.0。
有关详细信息，请参阅生成XML实例。
1
保留无用空格。
此样式设置将默认的xml:
space处理方式设置为与指定了xml:
space="preserve"的行为相同。
2
启用有限的内部DTD子集处理。
如果启用，则服务器可使用内部DTD子集提供的以下信息来执行非验证分析操作。
∙应用属性的默认值。
∙解析并扩展内部实体引用。
∙检查DTD内容模型以实现语法的正确性。
分析器将忽略外部DTD子集。
此外，不评估XML声明来查看standalone属性是设置为yes还是no，而是将XML实例当成一个独立文档进行分析。
3
保留无用空格，并启用有限的内部DTD子集处理。
返回类型
返回与data_type相同的值。
备注
隐式转换指那些没有指定CAST或CONVERT函数的转换。
显式转换指那些需要指定CAST或CONVERT函数的转换。
以下图例显示了可对SQLServer2005系统提供的数据类型执行的所有显式和隐式数据类型转换。
其中包括xml、bigint和sql_variant。
不存在对sql_variant数据类型的赋值进行的隐式转换，但是存在转换为sql_variant的隐式转换。
注意：
因为Unicode数据始终使用偶数个字节，所以在binary或varbinary与支持Unicode的数据类型之间进行转换时会使用警告。
例如，以下转换不返回十六进制值41；而是返回4100：
SELECTCAST（CAST（0x41ASnvarchar）ASvarbinary）。
大值数据类型
大值数据类型表现出与小值数据类型相同的隐式和显式转换行为，特别是varchar、nvarchar和varbinary数据类型。
但是，应该考虑以下原则：
∙从image到varbinary（max）的转换与反向转换是隐式转换，text与varchar（max）、ntext、nvarchar（max）之间的转换也是隐式转换。
∙从大值数据类型（如varchar（max））到小值数据类型（如varchar）的转换是隐式转换，但如果大值相对于指定长度的小值数据类型显得太大，则产生截断。
∙从varchar、nvarchar或varbinary到其相应的大值数据类型的转换都是隐式执行的。
∙从sql_variant数据类型到大值数据类型的转换是显式转换。
∙大值数据类型不能转换为sql_variant数据类型。
有关转换Microsoft.NETFramework公共语言运行时（CLR）用户定义类型的信息，请参阅对用户定义类型执行操作。
有关从xml数据类型进行转换的详细信息，请参阅生成XML实例。
xml数据类型
当您将xml数据类型显式或隐式转换为字符串或二进制数据类型时，xml数据类型的内容将根据一组规则进行序列化。
有关这些规则的信息，请参阅XML数据的序列化。
有关如何从XML转换为CLR用户定义类型的信息，请参阅对用户定义类型执行操作。
有关从其他数据类型转换为xml数据类型的信息，请参阅生成XML实例。
文本和图像数据类型
不支持对text和image数据类型进行自动数据类型转换。
可将text数据显式转换为字符数据，将image数据转换为binary或varbinary，但最大长度是8000字节。
如果试图进行不正确的转换，如将包含字母的字符表达式转换为int，则SQLServer将返回错误消息。
输出的排序规则
如果CAST或CONVERT的输出是字符串，并且输入也是字符串，则输出将与输入具有相同的排序规则和排序规则标签。
如果输入不是字符串，则输出采用数据库的默认排序规则以及强制默认的排序规则标签。
有关详细信息，请参阅排序规则优先级（Transact-SQL）。
若要为输出分配不同的排序规则，请将COLLATE子句应用于CAST或CONVERT函数的结果表达式。
例如：
SELECTCAST（'abc'ASvarchar（5））COLLATEFrench_CS_AS
截断结果和舍入结果
将字符或二进制表达式（char、nchar、nvarchar、varchar、binary或varbinary）转换为其他数据类型的表达式时，可截断数据，仅显示部分数据，或返回错误（因为结果太短而无法显示）。
除了下表显示的转换，其他到char、varchar、nchar、nvarchar、binary和varbinary的转换都将被截断。
被转换的数据类型
转换为的数据类型
结果
int、smallint或tinyint
char
*
varchar
*
nchar
E
nvarchar
E
money、smallmoney、numeric、decimal、float或real
char
E
varchar
E
nchar
E
nvarchar
E
*=结果长度太短而无法显示。
E=因为结果长度太短无法显示而返回错误。
SQLServer仅保证往返转换（即从原始数据类型进行转换后又返回原始数据类型的转换）在各版本间产生相同值。
以下示例显示的即是这样的往返转换：
复制代码
DECLARE@myvaldecimal（5,2）
SET@myval=193.57
SELECTCAST（CAST（@myvalASvarbinary（20））ASdecimal（10,5））
--Or,usingCONVERT
SELECTCONVERT（decimal（10,5）,CONVERT（varbinary（20）,@myval））
注意：
不要尝试构造binary值然后将其转换为数值数据类型类别的一种数据类型。
SQLServer不能保证decimal或numeric数据类型到binary的转换结果在SQLServer的各个版本中都相同。
以下示例显示了由于太小而无法显示的结果表达式。
复制代码
USEAdventureWorks;
GO
SELECTc.FirstName,c.LastName,SUBSTRING（c.Title,1,25）ASTitle,CAST（e.SickLeaveHoursASchar
（1））AS'SickLeave'
FROMHumanResources.EmployeeeJOINPerson.ContactcONe.EmployeeID=c.ContactID
WHERENOTEmployeeID>5
下面是结果集：
复制代码
FirstNameLastNameTitleSickLeave
------------------------------------------------
GustavoAchongMr.*
CatherineAbelMs.*
KimAbercrombieMs.*
HumbertoAcevedoSr.*
PilarAckermanSra.*
（5row（s）affected）
转换小数位数不同的数据类型时，结果值有时被截断，有时被舍入。
下表显示了此行为。
被转换的数据类型
转换为的数据类型
行为
numeric
numeric
舍入
numeric
int
截断
numeric
money
舍入
money
int
舍入
money
numeric
舍入
float
int
截断
float
numeric
舍入
float
datetime
舍入
datetime
int
舍入
例如，以下转换的结果为10：
SELECTCAST（10.6496ASint）
在进行数据类型转换时，若目标数据类型的小数位数小于源数据类型的小数位数，则该值将被截断。
例如，以下转换的结果为$10.3497：
SELECTCAST（10.3496847ASmoney）
当非数字型char、nchar、varchar或nvarchar数据转换为int、float、numeric或decimal时，SQLServer将返回错误消息。
当空字符串（""）转换为numeric或decimal时，SQLServer也返回错误。
转换二进制字符串数据
如果binary或varbinary数据转换为字符数据，并且在x后面指定了奇数位的值，则SQLServer将在x后面添加一个0（零）使其成为偶数位值。
二进制数据由从0到9和从A到F（或从a到f）的字符组成，每两个字符为一组。
二进制字符串必须以0x开头。
例如，若要输入FF，需要键入0xFF。
最大值是一个8000字节的二进制值，每个字节都是FF。
binary数据类型不能用于十六进制数据，而是用于位模式。
对于存储为二进制数据的十六进制数字的转换和计算结果，无法保证其可靠性。
指定binary数据类型的长度时，每两个字符被算作是一个单位长度。
长度10表示将输入10个双字符组。
由0x表示的空二进制字符串可以存储为二进制数据。
示例
A.同时使用CAST和CONVERT
每个示例都检索列表价格的第一位是3的产品的名称，并将ListPrice转换为int。
复制代码
--UseCAST
USEAdventureWorks;
GO
SELECTSUBSTRING（Name,1,30）ASProductName,ListPrice
FROMProduction.Product
WHERECAST（ListPriceASint）LIKE'3%';
GO
--UseCONVERT.
USEAdventureWorks;
GO
SELECTSUBSTRING（Name,1,30）ASProductName,ListPrice
FROMProduction.Product
WHERECONVERT（int,ListPrice）LIKE'3%';
GO
B.使用包含算术运算符的CAST
以下示例将本年度截止到现在的全部销售额（SalesYTD）除以佣金百分比（CommissionPCT），从而得出单列计算结果（Computed）。
在舍入到最接近的整数后，将此结果转换为int数据类型。
复制代码
USEAdventureWorks;
GO
SELECTCAST（ROUND（SalesYTD/CommissionPCT,0）ASint）AS'Computed'
FROMSales.SalesPerson
WHERECommissionPCT!
=0;
GO
下面是结果集：
复制代码
Computed
------
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107
（14row（s）affected）
C.使用CAST进行连接
以下示例使用CAST连接非字符型非二进制表达式。
复制代码
USEAdventureWorks;
GO
SELECT'Thelistpriceis'+CAST（ListPriceASvarchar（12））ASListPrice
FROMProduction.Product
WHEREListPriceBETWEEN350.00AND400.00;
GO
下面是结果集：
复制代码
ListPrice
------------------
Thelistpriceis357.06
Thelistpriceis364.09
Thelistpriceis364.09
Thelistpriceis364.09
Thelistpriceis364.09
（5row（s）affected）
D.使用CAST生成可读性更高的文本
以下示例使用选择列表中的CAST将Name列转换为char（10）列。
复制代码
USEAdventureWorks;
GO
SELECTDISTINCTCAST（p.NameASchar（10））ASName,s.UnitPrice
FROMSales.SalesOrderDetailsJOINProduction.Productpons.ProductID=p.ProductID
WHERENameLIKE'Long-SleeveLogoJersey,M';
GO
下面是结果集：
复制代码
NameUnitPrice
-------------------------------
Long-Sleev31.2437
Long-Sleev32.4935
Long-Sleev49.99
（3row（s）affected）
E.使用包含LIKE子句的CAST
以下示例将money列SalesYTD转换为int，然后再转换为char（20）列，以便可以将其用于LIKE子句。
复制代码
USEAdventureWorks;
GO
SELECTp.FirstName,p.LastName,s.SalesYTD,s.SalesPersonID
FROMPerson.ContactpJOINSales.SalesPersonsONp.ContactID=s.SalesPersonID
WHERECAST（CAST（s.SalesYTDASint）ASchar（20））LIKE'2%';
GO
下面是结果集：
复制代码
FirstNameLastNameSalesYTDSalesPersonID
----------------------------------------------------------------
CarolElliott2811012.7151279
JulieEstes219088.8836288
JanethEsteves2241204.0424289
（3
升级会员 