CAST 和 CONVERT (Transact-SQL)
更新日期: 2006 年 7 月 17 日
将一种数据类型的表达式显式转换为另一种数据类型的表达式。CAST 和 CONVERT 提供相似的功能。
Syntax for CAST: CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT: 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。注意:
SQL Server 支持本主题中列出的样式与 CONVERT 目标数据类型的所述组合。不支持所有其他样式和组合。请不要使用任何不支持的样式。如果使用不支持的样式或不支持的样式与目标数据类型的组合,则可能会返回错误或不可靠的结果。所有版本的 SQL Server 都不能保证这些结果的准确性。 SQL Server 通过使用科威特算法来支持阿拉伯样式的日期格式。
在下表中,左侧的两列表示将 datetime 或 smalldatetime 数据转换为字符数据的 style 值。将 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)。
不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3) -
0 或 100 (1,2)
默认设置
mon dd yyyy hh: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)
-
dd mon yy
7
107(1)
-
mon dd, yy
8
108
-
hh:mi:ss
-
9 或 109 (1,2)
默认设置 + 毫秒
mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10
110
美国
mm-dd-yy
11
111
日本
yy/mm/dd
12
112
ISO
yymmdd
-
13 或 113 (1,2)
欧洲默认设置 + 毫秒
dd mon yyyy hh:mi:ss:mmm(24h)
14
114
-
hh:mi:ss:mmm(24h)
-
20 或 120 (2)
ODBC 规范
yyyy-mm-dd hh:mi:ss(24h)
-
21 或 121 (2)
ODBC 规范(带毫秒)
yyyy-mm-dd hh: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)
dd mon yyyy hh:mi:ss:mmmAM
-
131 (2)
回历 (5)
dd/mm/yy hh: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 回历是有多种变体的日历系统。SQL Server 2005 使用科威特算法。
重要提示:
默认情况下,SQL Server 基于截止年份 2049 年来解释两位数的年份。换言之,就是将两位数的年份 49 解释为 2049,将两位数的年份 50 解释为 1950。许多客户端应用程序(如基于自动化对象的应用程序)都使用截止年份 2030 年。SQL Server 提供了“两位数年份截止”配置选项,可通过此选项更改 SQL Server 使用的截止年份,从而对日期进行一致处理。建议您指定四位数年份。 6 仅支持从字符数据转换为 datetime 或 smalldatetime。仅表示日期或时间成分的字符数据转换为 datetime 或 smalldatetime 数据类型时,未指定的时间成分设置为 00:00:00.000,未指定的日期成分设置为 1900-01-01。
7使用可选的时间区域指示符 (Z) 更便于将具有时区信息的 XML datetime 值映射到没有时区的 SQL Server datetime 值。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 值。
值
返回与 data_type 相同的值。
隐式转换指那些没有指定 CAST 或 CONVERT 函数的转换。显式转换指那些需要指定 CAST 或 CONVERT 函数的转换。以下图例显示了可对 SQL Server 2005 系统提供的数据类型执行的所有显式和隐式数据类型转换。其中包括 xml、bigint 和 sql_variant。不存在对 sql_variant 数据类型的赋值进行的隐式转换,但是存在转换为 sql_variant 的隐式转换。
![]() |
---|
因为 Unicode 数据始终使用偶数个字节,所以在 binary 或 varbinary 与支持 Unicode 的数据类型之间进行转换时会使用警告。例如,以下转换不返回十六进制值 41;而是返回 4100:SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary) 。 |
大值数据类型表现出与小值数据类型相同的隐式和显式转换行为,特别是 varchar、nvarchar 和 varbinary 数据类型。但是,应该考虑以下原则:
- 从 image 到 varbinary(max) 的转换与反向转换是隐式转换,text 与 varchar(max)、ntext、nvarchar(max) 之间的转换也是隐式转换。
- 从大值数据类型(如 varchar(max))到小值数据类型(如 varchar)的转换是隐式转换,但如果大值相对于指定长度的小值数据类型显得太大,则产生截断。
- 从 varchar、nvarchar 或 varbinary 到其相应的大值数据类型的转换都是隐式执行的。
- 从 sql_variant 数据类型到大值数据类型的转换是显式转换。
- 大值数据类型不能转换为 sql_variant 数据类型。
有关转换 Microsoft .NET Framework 公共语言运行时 (CLR) 用户定义类型的信息,请参阅对用户定义类型执行操作。有关从 xml 数据类型进行转换的详细信息,请参阅生成 XML 实例。
当您将 xml 数据类型显式或隐式转换为字符串或二进制数据类型时,xml 数据类型的内容将根据一组规则进行序列化。有关这些规则的信息,请参阅 XML 数据的序列化。有关如何从 XML 转换为 CLR 用户定义类型的信息,请参阅对用户定义类型执行操作。有关从其他数据类型转换为 xml 数据类型的信息,请参阅生成 XML 实例。
不支持对 text 和 image 数据类型进行自动数据类型转换。可将 text 数据显式转换为字符数据,将 image 数据转换为 binary 或 varbinary,但最大长度是 8000 字节。如果试图进行不正确的转换,如将包含字母的字符表达式转换为 int,则 SQL Server 将返回错误消息。
如果 CAST 或 CONVERT 的输出是字符串,并且输入也是字符串,则输出将与输入具有相同的排序规则和排序规则标签。如果输入不是字符串,则输出采用数据库的默认排序规则以及强制默认的排序规则标签。有关详细信息,请参阅排序规则优先级 (Transact-SQL)。
若要为输出分配不同的排序规则,请将 COLLATE 子句应用于 CAST 或 CONVERT 函数的结果表达式。例如:
SELECT CAST('abc' AS varchar(5)) COLLATE French_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 = 因为结果长度太短无法显示而返回错误。
SQL Server 仅保证往返转换(即从原始数据类型进行转换后又返回原始数据类型的转换)在各版本间产生相同值。以下示例显示的即是这样的往返转换:
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))
![]() |
---|
不要尝试构造 binary 值然后将其转换为数值数据类型类别的一种数据类型。SQL Server 不能保证 decimal 或 numeric 数据类型到 binary 的转换结果在 SQL Server 的各个版本中都相同。 |
以下示例显示了由于太小而无法显示的结果表达式。
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)
转换小数位数不同的数据类型时,结果值有时被截断,有时被舍入。下表显示了此行为。
被转换的数据类型 | 转换为的数据类型 | 行为 |
---|---|---|
numeric |
numeric |
舍入 |
numeric |
int |
截断 |
numeric |
money |
舍入 |
money |
int |
舍入 |
money |
numeric |
舍入 |
float |
int |
截断 |
float |
numeric |
舍入 |
float |
datetime |
舍入 |
datetime |
int |
舍入 |
例如,以下转换的结果为 10
:
SELECT CAST(10.6496 AS int)
在进行数据类型转换时,若目标数据类型的小数位数小于源数据类型的小数位数,则该值将被截断。例如,以下转换的结果为 $10.3497
:
SELECT CAST(10.3496847 AS money)
当非数字型 char、nchar、varchar 或 nvarchar 数据转换为 int、float、numeric 或 decimal 时,SQL Server 将返回错误消息。当空字符串 (" ") 转换为 numeric 或 decimal 时,SQL Server 也返回错误。
如果 binary 或 varbinary 数据转换为字符数据,并且在 x 后面指定了奇数位的值,则 SQL Server 将在 x 后面添加一个 0(零)使其成为偶数位值。
二进制数据由从 0 到 9 和从 A 到 F(或从 a 到 f)的字符组成,每两个字符为一组。二进制字符串必须以 0x 开头。例如,若要输入 FF,需要键入 0xFF。最大值是一个 8000 字节的二进制值,每个字节都是 FF。binary 数据类型不能用于十六进制数据,而是用于位模式。对于存储为二进制数据的十六进制数字的转换和计算结果,无法保证其可靠性。
指定 binary 数据类型的长度时,每两个字符被算作是一个单位长度。长度 10 表示将输入 10 个双字符组。
由 0x 表示的空二进制字符串可以存储为二进制数据。
每个示例都检索列表价格的第一位是 3
的产品的名称,并将 ListPrice
转换为 int
。
-- 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
以下示例将本年度截止到现在的全部销售额 (SalesYTD
) 除以佣金百分比 (CommissionPCT
),从而得出单列计算结果 (Computed
)。在舍入到最接近的整数后,将此结果转换为 int
数据类型。
USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
下面是结果集:
Computed
------
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107
(14 row(s) affected)
以下示例使用 CAST
连接非字符型非二进制表达式。
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
下面是结果集:
ListPrice
------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09
(5 row(s) affected)
以下示例使用选择列表中的 CAST
将 Name
列转换为 char(10)
列。
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
下面是结果集:
Name UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99
(3 row(s) affected)
以下示例将 money 列 SalesYTD
转换为 int
,然后再转换为 char(20)
列,以便可以将其用于 LIKE
子句。
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
下面是结果集:
FirstName LastName SalesYTD SalesPersonID
---------------- ------------------- ---------------- -------------
Carol Elliott 2811012.7151 279
Julie Estes 219088.8836 288
Janeth Esteves 2241204.0424 289
(3 row(s) affected)
下面的几个示例显示如何通过 xml 数据类型使用 CONVERT 转换为类型化的 XML。
此示例将包含空格、文本和标记的字符串转换为类型化的 XML,并删除所有无用空格(节点之间的边界空格):
CONVERT(XML, '<root><child/></root>')
此示例将包含空格、文本和标记的类似字符串转换为类型化的 XML,并保留无用空格(节点之间的边界空格):
CONVERT(XML, '<root> <child/> </root>', 1)
此示例将包含空格、文本和标记的字符串转换为类型化的 XML:
CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)
有关详细信息,请参阅生成 XML 实例。
以下示例显示了当前日期和时间,并使用 CAST
将当前日期和时间改为字符数据类型,然后使用 CONVERT
以 ISO 8901
格式显示日期和时间。
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO
下面是结果集:
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ------------------------------ ------------------------------
2006-04-18 09:58:04.570 Apr 18 2006 9:58AM 2006-04-18T09:58:04.570
(1 row(s) affected)
以下示例大致与上述示例相反。该示例将日期和时间显示为字符数据,并使用 CAST
将字符数据改为 datetime
数据类型,然后使用 CONVERT
将字符数据改为 datetime
数据类型。
SELECT
'2006-04-04T15:50:59.997' AS UnconvertedText,
CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO
下面是结果集:
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997
(1 row(s) affected)
SELECT (Transact-SQL)
系统函数 (Transact-SQL)
数据类型转换(数据库引擎)
ISO 8601 格式
编写国际化 Transact-SQL 语句
发布日期 | 历史记录 |
---|---|
2006 年 7 月 17 日 |
|
2006 年 4 月 14 日 |
|