CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

这些函数将表达式由一种数据类型转换为另一种数据类型。These functions convert an expression of one data type to another.

语法Syntax

-- CAST Syntax:  
CAST ( expression AS data_type [ ( length ) ] )  
  
-- CONVERT Syntax:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  

“主题链接”图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

参数Arguments

expressionexpression
任何有效的表达式Any valid expression.

data_type data_type
目标数据类型。The target data type. 这包括 xml、bigint 和sql_variant 。This includes xml, bigint, and sql_variant. 不能使用别名数据类型。Alias data types cannot be used.

lengthlength
指定目标数据类型长度的可选整数,适用于允许用户指定长度的数据类型。An optional integer that specifies the length of the target data type, for data types that allow a user specified length. 默认值为 30。The default value is 30.

style style
指定 CONVERT 函数将如何转换 expression 的整数表达式 。An integer expression that specifies how the CONVERT function will translate expression. 对于 NULL 的样式值,则返回 NULL。For a style value of NULL, NULL is returned. data_type 确定范围 。data_type determines the range.

返回类型Return types

返回转换为 data_type 的 expression 。Returns expression, translated to data_type.

日期和时间样式Date and Time styles

对于日期或时间数据类型的 expression,style 可以具有下表所示的某个值 。For a date or time data type expression, style can have one of the values shown in the following table. 其他值作为 0 进行处理。Other values are processed as 0. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,在从日期和时间类型转换为 datetimeoffset 时支持的唯一样式是 0 或 1 。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the only styles supported, when converting from date and time types to datetimeoffset, are 0 or 1. 所有其他转换样式均返回错误 9809。All other conversion styles return error 9809.

备注

SQL ServerSQL Server 使用科威特算法来支持阿拉伯样式的日期格式。supports the date format, in Arabic style, with the Kuwaiti algorithm.

不带世纪数位 (yy) (1)Without century (yy) (1) 带世纪数位 (yyyy)With century (yyyy) StandardStandard 输入/输出 (3)Input/Output (3)
- 0 或 100 (1,2) 0 or 100 (1,2) datetime 和 smalldatetime 的默认值Default for datetime and smalldatetime mon dd yyyy hh:miAM(或 PM)mon dd yyyy hh:miAM (or PM)
11 101101 美国U.S. 1 = mm/dd/yy1 = mm/dd/yy
101 = mm/dd/yyyy101 = mm/dd/yyyy
22 102102 ANSIANSI 2 = yy.mm.dd2 = yy.mm.dd
102 = yyyy.mm.dd102 = yyyy.mm.dd
33 103103 英国/法国British/French 3 = dd/mm/yy3 = dd/mm/yy
103 = dd/mm/yyyy103 = dd/mm/yyyy
44 104104 德语German 4 = dd.mm.yy4 = dd.mm.yy
104 = dd.mm.yyyy104 = dd.mm.yyyy
55 105105 意大利语Italian 5 = dd-mm-yy5 = dd-mm-yy
105 = dd-mm-yyyy105 = dd-mm-yyyy
66 106 (1)106 (1) - 6 = dd mon yy6 = dd mon yy
106 = dd mon yyyy106 = dd mon yyyy
77 107 (1)107 (1) - 7 = Mon dd, yy7 = Mon dd, yy
107 = Mon dd, yyyy107 = Mon dd, yyyy
8 或 24 8 or 24 108108 - hh:mi:sshh:mi:ss
- 9 或 109 (1,2) 9 or 109 (1,2) 默认格式 + 毫秒Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM(或 PM)mon dd yyyy hh:mi:ss:mmmAM (or PM)
1010 110110 USAUSA 10 = mm-dd-yy10 = mm-dd-yy
110 = mm-dd-yyyy110 = mm-dd-yyyy
1111 111111 日本JAPAN 11 = yy/mm/dd11 = yy/mm/dd
111 = yyyy/mm/dd111 = yyyy/mm/dd
1212 112112 ISOISO 12 = yymmdd12 = yymmdd
112 = yyyymmdd112 = yyyymmdd
- 13 或 113 (1,2) 13 or 113 (1,2) 欧洲默认格式 + 毫秒Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24 小时制)dd mon yyyy hh:mi:ss:mmm (24h)
1414 114114 - hh:mi:ss:mmm(24 小时制)hh:mi:ss:mmm (24h)
- 20 或 120 (2) 20 or 120 (2) ODBC 规范ODBC canonical yyyy-mm-dd hh:mi:ss(24 小时制)yyyy-mm-dd hh:mi:ss (24h)
- 21、25 或 121 (2)21 or 25 or 121 (2) time、date、datetime2 和 datetimeoffset 的 ODBC 规范(带毫秒)默认值ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset yyyy-mm-dd hh:mi:ss.mmm(24 小时制)yyyy-mm-dd hh:mi:ss.mmm (24h)
22 22 - 美国U.S. mm/dd/yy hh:mi:ss AM(或 PM)mm/dd/yy hh:mi:ss AM (or PM)
- 23 23 ISO8601ISO8601 yyyy-mm-ddyyyy-mm-dd
- 126 (4)126 (4) ISO8601ISO8601 yyyy-mm-ddThh:mi:ss.mmm(无空格)yyyy-mm-ddThh:mi:ss.mmm (no spaces)

注意: 毫秒 (mmm) 值为 0 时,不会显示毫秒小数部分的值。Note: For a milliseconds (mmm) value of 0, the millisecond decimal fraction value will not display. 例如,值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。For example, the value '2012-11-07T18:26:20.000 displays as '2012-11-07T18:26:20'.
- 127(6, 7)127(6, 7) 带时区 Z 的 ISO8601。ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ(无空格)yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)

注意: 毫秒 (mmm) 值为 0 时,不会显示毫秒小数值。Note: For a milliseconds (mmm) value of 0, the millisecond decimal value will not display. 例如,值“2012-11-07T18:26:20.000”显示为“2012-11-07T18:26:20”。For example, the value '2012-11-07T18:26:20.000 will display as '2012-11-07T18:26:20'.
- 130 (1,2)130 (1,2) 回历 (5)Hijri (5) dd mon yyyy hh:mi:ss:mmmAMdd mon yyyy hh:mi:ss:mmmAM

在此样式中,mon 表示完整月份名称的多标记回历 unicode 表示形式 。In this style, mon represents a multi-token Hijri unicode representation of the full month name. 该值在 SSMS 的默认 US 安装中不会正确呈现。This value does not render correctly on a default US installation of SSMS.
- 131 (2)131 (2) 回历 (5)Hijri (5) dd/mm/yyyy hh:mi:ss:mmmAMdd/mm/yyyy hh:mi:ss:mmmAM

1 这些样式值返回不确定的结果。1 These style values return nondeterministic results. 包括所有 (yy)(不带世纪数位)样式和一部分 (yyyy)(带世纪数位)样式。Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 默认值(0 或 100、9 或 109、13 或 113、20 或 120,23,以及 21、25 或 121)始终返回世纪位数 (yyyy) 。2 The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, 23, and 21 or 25 or 121) always return the century (yyyy).

3 转换为 datetime 时输入;转换为字符数据时输出 。3 Input when you convert to datetime; output when you convert to character data.

4 为用于 XML 而设计。4 Designed for XML use. 对于从 datetime 或 smalldatetime 到字符数据的转换,请参阅上一个表,查看输出格式 。For conversion from datetime or smalldatetime to character data, see the previous table for the output format.

5 回历是有多种变体的日历系统。5 Hijri is a calendar system with several variations. SQL ServerSQL Server 使用科威特算法。uses the Kuwaiti algorithm.

重要

默认情况下,SQL ServerSQL Server 基于截止年份 2049 年来解释两位数的年份。By default, SQL ServerSQL Server interprets two-digit years based on a cutoff year of 2049. 这意味着 SQL ServerSQL Server 将两位数年份 49 解释为 2049 年,将两位数年份 50 解释为 1950 年。That means that SQL ServerSQL Server interprets the two-digit year 49 as 2049 and the two-digit year 50 as 1950. 许多客户端应用程序(包括基于自动化对象的应用程序)都使用截止年份 2030 年。Many client applications, including those based on Automation objects, use a cutoff year of 2030. SQL ServerSQL Server 提供两位数年份截止配置选项来更改 SQL ServerSQL Server 所使用的截止年份。provides the two digit year cutoff configuration option to change the cutoff year used by SQL ServerSQL Server. 这允许对日期进行一致处理。This allows for the consistent treatment of dates. 建议您指定四位数年份。We recommend specifying four-digit years.

6 仅在从字符数据强制转换到 datetime 或 smalldatetime 时提供支持 。6 Only supported when casting from character data to datetime or smalldatetime. 仅表示日期或时间成分的字符数据强制转换为 datetime 或 smalldatetime 数据类型时,未指定的时间成分设置为 00:00:00.000,未指定的日期成分设置为 1900-01-01 。When casting character data representing only date or only time components to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

7 使用可选的时区指示符 Z 更便于将具有时区信息的 XML datetime 值映射到没有时区的 SQL ServerSQL Server datetime 值 。7 Use the optional time zone indicator Z to make it easier to map XML datetime values that have time zone information to SQL ServerSQL Server datetime values that have no time zone. Z 指示时区 UTC-0。Z indicates time zone UTC-0. + 或 - 方向的 HH:MM 偏移量则指示其他时区。The HH:MM offset, in the + or - direction, indicates other time zones. 例如: 2006-12-12T23:45:12-08:00For example: 2006-12-12T23:45:12-08:00.

将 smalldatetime 转换为字符数据时,包含秒或毫秒的样式将在这些位置上显示零 。When converting smalldatetime to character data, the styles that include seconds or milliseconds show zeros in these positions. 从 datetime 或 smalldatetime 值转换时,可以使用合适的 char 或 varchar 数据类型长度截断不需要的日期部分 。When converting from datetime or smalldatetime values, use an appropriate char or varchar data type length to truncate unwanted date parts.

使用包含时间的样式将字符数据转换为 datetimeoffset 时,将在结果末尾追加时区偏移量 。When converting character data to datetimeoffset, using a style that includes a time, a time zone offset is appended to the result.

float 和 real 样式float and real styles

对于 float 或 real 的 expression,style 可能具有下表显示的值之一 。For a float or real expression, style can have one of the values shown in the following table. 其他值作为 0 进行处理。Other values are processed as 0.

ReplTest1Value “输出”Output
0 (默认值)0 (default) 最多包含 6 位。A maximum of 6 digits. 根据需要使用科学记数法。Use in scientific notation, when appropriate.
11 始终为 8 位值。Always 8 digits. 始终使用科学记数法。Always use in scientific notation.
22 始终为 16 位值。Always 16 digits. 始终使用科学记数法。Always use in scientific notation.
33 始终为 17 位值。Always 17 digits. 用于无损转换。Use for lossless conversion. 使用此样式,可以保证每个不重复的 float 或 real 值转换为不重复的字符串。With this style, every distinct float or real value is guaranteed to convert to a distinct character string.

适用范围: SQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始)和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (Starting in SQL Server 2016 (13.x)SQL Server 2016 (13.x)) and Azure SQL DatabaseAzure SQL Database.
126, 128, 129126, 128, 129 为了保持向后兼容而包括在内,而以后的版本可能不推荐这些值。Included for legacy reasons; a future release could deprecate these values.

money 和 smallmoney 样式money and smallmoney styles

对于 money 和 smallmoney 的 expression,style 可能具有下表显示的值之一 。For a money or smallmoney expression, style can have one of the values shown in the following table. 其他值作为 0 进行处理。Other values are processed as 0.

ReplTest1Value “输出”Output
0 (默认值)0 (default) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point

例如:4235.98。Example: 4235.98.
11 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point

例如:3,510.92。Example: 3,510.92.
22 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point

例如:4235.9819。Example: 4235.9819.
126126 转换为 char(n) 或 varchar(n) 时,等同于样式 2Equivalent to style 2, when converting to char(n) or varchar(n)

xml 样式xml styles

对于 xml 的 expression,style 可能具有下表显示的值之一 。For an xml expression, style can have one of the values shown in the following table. 其他值作为 0 进行处理。Other values are processed as 0.

ReplTest1Value “输出”Output
0 (默认值)0 (default) 使用默认的分析行为,即放弃无用的空格,且不允许使用内部 DTD 子集。Use default parsing behavior that discards insignificant white space, and does not allow for an internal DTD subset.

注意: 转换为 xml 数据类型时,SQL ServerSQL Server 的无用空格处理方式不同于 XML 1.0 。Note: When converting to the xml data type, SQL ServerSQL Server insignificant white space is handled differently than in XML 1.0. 有关详细信息,请参阅创建 XML 数据的实例For more information, see Create Instances of XML Data.
11 保留无用空格。Preserve insignificant white space. 此样式设置将默认 xml:space 处理设置为匹配 xml:space="preserve" 的行为 。This style setting sets the default xml:space handling to match the behavior of xml:space="preserve".
22 启用有限的内部 DTD 子集处理。Enable limited internal DTD subset processing.

如果启用,则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。If enabled, the server can use the following information that is provided in an internal DTD subset, to perform nonvalidating parse operations.

- 应用属性的默认值- Defaults for attributes are applied
- 解析并展开内部实体引用- Internal entity references are resolved and expanded
- 检查 DTD 内容模型来确定语法的正确性- The DTD content model is checked for syntactical correctness

分析器忽略外部 DTD 子集。The parser ignores external DTD subsets. 此外,它不会评估 XML 声明来查看 standalone 属性具有 yes 值还是 no 值 。Also, it does not evaluate the XML declaration to see whether the standalone attribute has a yes or no value. 相反,它将 XML 实例解析为独立文档。Instead, it parses the XML instance as a stand-alone document.
33 保留无用空格,并启用有限的内部 DTD 子集处理。Preserve insignificant white space, and enable limited internal DTD subset processing.

二进制样式Binary styles

对于 binary(n)、varbinary(n)、char(n) 或 varchar(n) 的 expression,style 可能具有下表显示的值之一 。For a binary(n), char(n), varbinary(n), or varchar(n) expression, style can have one of the values shown in the following table. 表中没有列出样式值将返回错误。Style values not listed in the table will return an error.

ReplTest1Value “输出”Output
0 (默认值)0 (default) 将 ASCII 字符转换为二进制字节,或者将二进制字节转换为 ASCII 字符。Translates ASCII characters to binary bytes, or binary bytes to ASCII characters. 每个字符或字节按照 1:1 进行转换。Each character or byte is converted 1:1.

对于二进制 data_type,则会在结果左侧添加字符 0x 。For a binary data_type, the characters 0x are added to the left of the result.
1, 21, 2 对于二进制 data_type,则表达式必须为字符表达式 。For a binary data_type, the expression must be a character expression. expression 必须具有偶数数量的十六进制数字(0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f) 。The expression must have an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). 如果将 style 设置为 1,则 0x 必须作为前两个字符 。If the style is set to 1, the must have 0x as the first two characters. 如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。If the expression contains an odd number of characters, or if any of the characters is invalid, an error is raised.

如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果 。If the length of the converted expression exceeds the length of the data_type, the result is right truncated.

如果固定长度 data_types 大于转换后的结果,则在结果右侧添加零 。Fixed length data_types larger than the converted result have zeros added to the right of the result.

字符类型的 data_type 要求二进制表达式 。A data_type of type character requires a binary expression. 每个二进制字符均转换为两个十六进制字符。Each binary character is converted into two hexadecimal characters. 如果转换后的表达式长度大于 data_type 长度,则会在右侧将其截断 。If the length of the converted expression exceeds the length of the data_type, it will be right truncated.

对于固定大小的字符类型 data_type ,并且转换后的结果长度小于其 data_type 长度,则会在转换后的表达式右侧添加空格,以使十六进制数字的个数保持为偶数 。For a fixed size character type data_type, if the length of the converted result is less than its length of the data_type, spaces are added to the right of the converted expression, to maintain an even number of hexadecimal digits.

对于 style 1,将在转换后的结果左侧添加字符 0x 。The characters 0x will be added to the left of the converted result for style 1.

隐式转换Implicit conversions

隐式转换不需要规范 CAST 函数或 CONVERT 函数。Implicit conversions do not require specification of either the CAST function or the CONVERT function. 显示转换需要规范 CAST 函数或 CONVERT 函数。Explicit conversions require specification of the CAST function or the CONVERT function. 以下图例显示了可对 SQL ServerSQL Server 系统提供的数据类型执行的所有显式和隐式数据类型转换。The following illustration shows all explicit and implicit data type conversions allowed for SQL ServerSQL Server system-supplied data types. 这些包括 bigint、sql_variant 和 xml 。These include bigint, and sql_variant, and xml. 不存在对 sql_variant 数据类型的赋值进行的隐式转换,但是存在转换为 sql_variant 的隐式转换 。There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

提示

可从 Microsoft 下载中心将此图表下载为 PDF 文件。The Microsoft Download Center has this chart available for download as a PDF file.

数据类型转换表Data type conversion table

虽然上面的图表说明了 SQL ServerSQL Server 中允许的所有显式和隐式转换,但转换生成的数据类型取决于执行的操作:The above chart illustrates all the explicit and implicit conversions that are allowed in SQL ServerSQL Server, but the resulting data type of the conversion depends on the operation being performed:

  • 对于显式转换,语句本身会确定生成的数据类型。For explicit conversions, the statement itself determines the resulting data type.
  • 对于隐式转换,赋值语句(例如设置变量的值或在列中插入值)将生成由变量声明或列定义所定义的数据类型。For implicit conversions, assignment statements such as setting the value of a variable or inserting a value into a column will result in the data type that was defined by the variable declaration or column definition.
  • 对于比较运算符或其他表达式,生成的数据类型取决于数据类型优先级的规则。For comparison operators or other expressions, the resulting data type will depend on the rules of data type precedence.

提示

有关转换中数据类型优先级的影响的实际示例可在本部分后面看到。A practical example on the effects of data type precedence in conversions can be seen later in this section.

在 datetimeoffset 与字符类型 char、nchar、nvarchar 和 varchar 之间转换时,转换后的时区偏移量部分的 HH 和 MM 都应始终为两个数字 。When you convert between datetimeoffset and the character types char, nchar, nvarchar, and varchar, the converted time zone offset part should always have double digits for both HH and MM. 例如 -08:00。For example, -08:00.

备注

因为 Unicode 数据始终使用偶数个字节,所以在 binary 或 varbinary 与支持 Unicode 的数据类型之间进行转换时要特别小心 。Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. 例如,以下转换不返回十六进制值 41。For example, the following conversion does not return a hexadecimal value of 41. 而是返回十六进制值 4100:SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)It returns a hexadecimal value of 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary). 有关详细信息,请参阅 排序规则和 Unicode 支持For more information, see Collation and Unicode Support.

大值数据类型Large-value data types

大值数据类型具有与小值数据类型相同的隐式和显式转换行为 - 特别是 nvarchar、varbinary 和 varchar 数据类型 。Large-value data types have the same implicit and explicit conversion behavior as their smaller counterparts - specifically, the nvarchar, varbinary, and varchar data types. 但是,请考虑以下原则:However, consider the following guidelines:

  • 从 image 转换到 varbinary(max) 以及从 varbinary(max) 转换到 image 属于隐式转换操作,同样的还有 text 与 varchar(max) 之间的转换和 ntext 与 nvarchar(max) 之间的转换 。Conversion from image to varbinary(max), and vice-versa, operates as an implicit conversion, as do conversions between text and varchar(max), and ntext and nvarchar(max).
  • 从大值数据类型(如 varchar(max))到小值数据类型(如 varchar)的转换是隐式转换,但如果大值的大小超过小值数据类型的指定长度,则产生截断 。Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation occurs if the size of the large value exceeds the specified length of the smaller data type.
  • 从 nvarchar、varbinary 或 varchar 到其相应的大值数据类型的转换都是隐式转换 。Conversion from nvarchar, varbinary, or varchar to their corresponding large-value data types happens implicitly.
  • 从 sql_variant 数据类型到大值数据类型的转换是显式转换 。Conversion from the sql_variant data type to the large-value data types is an explicit conversion.
  • 大值数据类型不能转换为 sql_variant 数据类型 。Large-value data types cannot be converted to the sql_variant data type.

有关从 xml 数据类型进行转换的详细信息,请参阅创建 XML 数据的实例For more information about conversion from the xml data type, see Create Instances of XML Data.

XML 数据类型xml data type

将 xml 数据类型显式或隐式转换为字符串或二进制数据类型时,xml 数据类型的内容将根据一组确定的规则进行序列化 。When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a defined set of rules. 有关这些规则的信息,请参阅定义 XML 数据的序列化For information about these rules, see Define the Serialization of XML Data. 有关从其他数据类型转换到 xml 数据类型对的信息,请参阅创建 XML 数据的实例For information about conversion from other data types to the xml data type, see Create Instances of XML Data.

text 和 image 数据类型text and image data types

text 和 image 数据类型不支持自动进行数据类型转换 。The text and image data types do not support automatic data type conversion. 可以显式将 text 数据转换为字符数据,将 image 数据转换为 binary 或 varbinary,但是最大长度为 8000 字节 。You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. 如果试图进行不正确的转换(如将包含字母的字符表达式转换为 int),则 SQL ServerSQL Server 将返回错误消息 。If you try an incorrect conversion, for example trying to convert a character expression that includes letters to an int, SQL ServerSQL Server returns an error message.

输出排序规则Output Collation

如果 CAST 或 CONVERT 输出字符串,并且接收字符串输入,则输出将与输入具有相同的排序规则和排序规则标签。When the CAST or CONVERT functions output a character string, and they receive a character string input, the output has the same collation and collation label as the input. 如果输入不是字符串,则输出采用数据库的默认排序规则以及强制默认的排序规则标签。If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. 有关详细信息,请参阅排序规则优先顺序 (Transact-SQL)For more information, see Collation Precedence (Transact-SQL).

若要为输出分配不同的排序规则,请将 COLLATE 子句应用于 CAST 或 CONVERT 函数的结果表达式。To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. 例如:For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

截断结果和舍入结果Truncating and rounding results

将字符或二进制表达式(binary、char、nchar、nvarchar、varbinary 或 varchar)转换为不同数据类型的表达式时,转换操作可能会截断输出数据,仅显示部分输出数据,或返回错误 。When converting character or binary expressions (binary, char, nchar, nvarchar, varbinary, or varchar) to an expression of a different data type, the conversion operation could truncate the output data, only partially display the output data, or return an error. 如果结果太短而无法显示,则会发生这种情况。These cases will occur if the result is too short to display. 会截断到 binary、char、nchar、nvarchar、varbinary 或 varchar 的转换,除了下表中显示的转换 。Conversions to binary, char, nchar, nvarchar, varbinary, or varchar are truncated, except for the conversions shown in the following table.

被转换的数据类型From data type 转换为的数据类型To data type 结果Result
int、smallint 或 tinyint int, smallint, or tinyint charchar *
varcharvarchar *
ncharnchar EE
nvarcharnvarchar EE
money、smallmoney、numeric、decimal、float 或 real money, smallmoney, numeric, decimal, float, or real charchar EE
varcharvarchar EE
ncharnchar EE
nvarcharnvarchar EE

* = 结果长度太短而无法显示* = Result length too short to display

E = 因为结果长度太短无法显示而返回错误。E = Error returned because result length is too short to display.

SQL ServerSQL Server 仅保证往返转换(也就是从原始数据类型进行转换后又返回原始数据类型的转换)在各版本间产生相同值。guarantees that only roundtrip conversions, in other words conversions that convert a data type from its original data type and back again, yield the same values from version to version. 以下示例显示的即是这样的往返转换:The following example shows such a roundtrip conversion:

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 值然后将其转换为数值数据类型类别的一种数据类型 。Do not construct binary values, and then convert them to a data type of the numeric data type category. SQL ServerSQL Server 不能保证 decimal 或 numeric 数据类型到 binary 的转换结果在 SQL ServerSQL Server 的各个版本中都相同 。does not guarantee that the result of a decimal or numeric data type conversion, to binary, will be the same between versions of SQL ServerSQL Server.

以下示例显示了由于太小而无法显示的结果表达式。The following example shows a resulting expression that is too small to display.

USE AdventureWorks2012;  
GO  
SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title,
    CAST(e.SickLeaveHours AS char(1)) AS [Sick Leave]  
FROM HumanResources.Employee e JOIN Person.Person p 
    ON e.BusinessEntityID = p.BusinessEntityID  
WHERE NOT e.BusinessEntityID >5;  

下面是结果集:Here is the result set.

FirstName   LastName      Title   Sick Leave
---------   ------------- ------- --------`
Ken         Sanchez       NULL   *
Terri       Duffy         NULL   *
Roberto     Tamburello    NULL   *
Rob         Walters       NULL   *
Gail        Erickson      Ms.    *

(5 row(s) affected)  

转换小数位数不同的数据类型时,SQL ServerSQL Server 有时会返回截断后的结果值,有时会返回舍入值。When you convert data types that differ in decimal places, SQL ServerSQL Server will sometimes return a truncated result value, and at other times it will return a rounded value. 此表显示了此行为。This table shows the behavior.

FromFrom 若要To 行为Behavior
numericnumeric numericnumeric 舍入Round
numericnumeric intint 截断Truncate
numericnumeric moneymoney 舍入Round
moneymoney intint 舍入Round
moneymoney numericnumeric 舍入Round
floatfloat intint 截断Truncate
floatfloat numericnumeric 舍入Round

如果将使用科学记数法的 float 值转换为 decimal 或 numeric 时,转换会限制为只有 17 位精度的值 。Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. 精度高于 17 的任何值都将舍入为零。Any value with precision higher than 17 rounds to zero.
floatfloat datetimedatetime 舍入Round
datetimedatetime intint 舍入Round

例如,10.6496 和 -10.6496 可能会被截断或者在转换到 int 或 numeric 类型期间被舍入 :For example, the values 10.6496 and -10.6496 may be truncated or rounded during conversion to int or numeric types:

SELECT  CAST(10.6496 AS int) as trunc1,
         CAST(-10.6496 AS int) as trunc2,
         CAST(10.6496 AS numeric) as round1,
         CAST(-10.6496 AS numeric) as round2;

查询结果显示在下表中:Results of the query are shown in the following table:

trunc1trunc1 trunc2trunc2 round1round1 round2round2
1010 -10-10 1111 -11-11

在进行数据类型转换时,若目标数据类型的小数位数小于源数据类型的小数位数,则该值将进行舍入。When converting data types where the target data type has fewer decimal places than the source data type, the value is rounded. 例如,此转换返回 $10.3497For example, this conversion returns $10.3497:

SELECT CAST(10.3496847 AS money);

将非数字 char、nchar、nvarchar 或 varchar 数据转换为 decimal、float、int、numeric 时,SQL ServerSQL Server 返回错误消息 。SQL ServerSQL Server returns an error message when converting nonnumeric char, nchar, nvarchar, or varchar data to decimal, float, int, numeric. 当空字符串 (" ") 转换为 numeric 或 decimal 时,SQL ServerSQL Server 也返回错误 。SQL ServerSQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

某些日期时间的转换具有不确定性Certain datetime conversions are nondeterministic

下表列出了从 string 到 datetime 的转换为不确定性转换的样式。The following table lists the styles for which the string-to-datetime conversion is nondeterministic.

低于 100 的所有样式1All styles below 1001 106106
107107 109109
113113 130130

1 样式 20 和 21 除外1 With the exception of styles 20 and 21

有关详细信息,请参阅文字日期字符串转换为日期值的不确定性转换For more information, see Nondeterministic conversion of literal date strings into DATE values.

补充字符(代理项对)Supplementary characters (surrogate pairs)

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始,使用增补字符 (SC) 排序规则时,从 nchar 或 nvarchar 到更小长度的 nchar 或 nvarchar 类型的 CAST 操作将不会在代理项对内截断 。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), when using supplementary character (SC) collations, a CAST operation from nchar or nvarchar to an nchar or nvarchar type of smaller length will not truncate inside a surrogate pair. 相反,该操作会在增补字符前面截断。Instead, the operation truncates before the supplementary character. 例如,以下代码段导致 @x 仅保留 'ab'For example, the following code fragment leaves @x holding just 'ab'. 没有足够的空间来保留增补字符。There is not enough space to hold the supplementary character.

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);  
SELECT CAST (@x AS NVARCHAR(3));  

在使用 SC 排序规则时,CONVERT 行为类似于 CASTWhen using SC collations, the behavior of CONVERT, is analogous to that of CAST. 有关详细信息,请参阅排序规则和 Unicode 支持 - 补充字符For more information, see Collation and Unicode Support - Supplementary Characters.

兼容性支持Compatibility support

SQL ServerSQL Server 的早期版本中,对 time 或 datetime2 数据类型的 CAST 和 CONVERT 操作的默认样式为 121,当在计算列表达式中使用这些类型时除外 。In earlier versions of SQL ServerSQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121, except when either type is used in a computed column expression. 对于计算列,默认样式为 0。For computed columns, the default style is 0. 当创建用于涉及自动参数化的查询中或约束定义中的计算列时,此行为会影响计算列。This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

兼容性级别为 110 或更高时,对 time 和 datetime2 数据类型的 CAST 和 CONVERT 操作的默认样式始终为 121 。Under compatibility level 110 and higher, the CAST and CONVERT operations on the time and datetime2 data types always have 121 as the default style. 如果查询依赖旧行为,请使用低于 110 的兼容性级别或在受影响的查询中显式指定 0 样式。If a query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

兼容性级别Compatibility level value CAST 和 CONVERT 的默认样式1Default style for CAST and CONVERT1 计算列的默认样式Default style for computed column
< 110 < 110 121121 00
> = 110 > = 110 121121 121121

1 计算列除外1 Except for computed columns

将数据库升级到兼容级别 110 或更高将不更改已存储到磁盘的用户数据。Upgrading the database to compatibility level 110 and higher will not change user data that has been stored to disk. 您必须相应手动更正此数据。You must manually correct this data as appropriate. 例如,如果使用了 SELECT INTO 来从包含上述计算列表达式的源创建表,将存储数据(使用样式 0)而非存储计算列定义本身。For example, if you used SELECT INTO to create a table from a source containing a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. 必须手动更新此数据,以匹配样式 121。You must manually update this data to match style 121.

示例Examples

A.A. 同时使用 CAST 和 CONVERTUsing both CAST and CONVERT

每个示例检索标价的第一位是 3 的产品的名称,并将其 ListPrice 值转换为 intThese examples retrieve the name of the product, for those products that have a 3 as the first digit of list price, and converts their ListPrice values to int.

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

下面是结果集:Here is the result set. CAST 和 CONVERT 的示例结果集相同。The sample result set is the same for both CAST and CONVERT.

ProductName                    ListPrice
------------------------------ ---------------------
LL Road Frame - Black, 58      337.22
LL Road Frame - Black, 60      337.22
LL Road Frame - Black, 62      337.22
LL Road Frame - Red, 44        337.22
LL Road Frame - Red, 48        337.22
LL Road Frame - Red, 52        337.22
LL Road Frame - Red, 58        337.22
LL Road Frame - Red, 60        337.22
LL Road Frame - Red, 62        337.22
LL Road Frame - Black, 44      337.22
LL Road Frame - Black, 48      337.22
LL Road Frame - Black, 52      337.22
Mountain-100 Black, 38         3374.99
Mountain-100 Black, 42         3374.99
Mountain-100 Black, 44         3374.99
Mountain-100 Black, 48         3374.99
HL Road Front Wheel            330.06
LL Touring Frame - Yellow, 62  333.42
LL Touring Frame - Blue, 50    333.42
LL Touring Frame - Blue, 54    333.42
LL Touring Frame - Blue, 58    333.42
LL Touring Frame - Blue, 62    333.42
LL Touring Frame - Yellow, 44  333.42
LL Touring Frame - Yellow, 50  333.42
LL Touring Frame - Yellow, 54  333.42
LL Touring Frame - Yellow, 58  333.42
LL Touring Frame - Blue, 44    333.42
HL Road Tire                   32.60

(28 rows affected)

B.B. 将 CAST 与算术运算符结合使用Using CAST with arithmetic operators

此示例将本年度截止到现在的全部销售额 (Computed) 除以佣金百分比 (SalesYTD),从而得出单列计算结果 (CommissionPCT)。This example calculates a single column computation (Computed) by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). 此值舍入为最接近的整数,然后 CAST 为 int 数据类型。This value is rounded to the nearest whole number and is then CAST to an int data type.

USE AdventureWorks2012;  
GO  
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS Computed  
FROM Sales.SalesPerson   
WHERE CommissionPCT != 0;  
GO  

下面是结果集:Here is the result set.

Computed
------
379753754
346698349
257144242
176493899
281101272
0  
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107

(14 row(s) affected)  

C.C. 使用 CAST 进行连接Using CAST to concatenate

此示例使用 CAST 连接非字符型表达式。This example concatenates noncharacter expressions by using CAST. 它使用 AdventureWorksDW 数据库。It uses the AdventureWorksDW database.

SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice  
FROM dbo.DimProduct  
WHERE ListPrice BETWEEN 350.00 AND 400.00;  

下面是结果集:Here is the result set.

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  

D.D. 使用 CAST 生成可读性更高的文本Using CAST to produce more readable text

此示例使用 SELECT 列表中的 CAST 将 Name 列转换为 char(10) 列 。This example uses CAST in the SELECT list, to convert the Name column to a char(10) column. 它使用 AdventureWorksDW 数据库。It uses the AdventureWorksDW database.

SELECT DISTINCT CAST(EnglishProductName AS char(10)) AS Name, ListPrice  
FROM dbo.DimProduct  
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';  

下面是结果集:Here is the result set.

Name        ListPrice
----------  ---------
Long-Sleev  31.2437
Long-Sleev  32.4935
Long-Sleev  49.99  

E.E. 将 CAST 与 LIKE 子句一起作用Using CAST with the LIKE clause

此示例将 moneySalesYTD 值转换为数据类型 int,然后转换为数据类型 char(20),以便 LIKE 子句可以使用。This example converts the money column SalesYTD values to data type int, and then to data typechar(20), so that the LIKE clause can use it.

USE AdventureWorks2012;  
GO  
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  

下面是结果集:Here is the result set.

FirstName        LastName            SalesYTD         BusinessEntityID
---------------- ------------------- ---------------- -------------
Tsvi             Reiter              2811012.7151      279
Syed             Abbas               219088.8836       288
Rachel           Valdez              2241204.0424      289

(3 row(s) affected)  

F.F. 使用 CONVERT 或 CAST 转换为类型化的 XMLUsing CONVERT or CAST with typed XML

这些示例说明如何通过 XML 数据类型和列 (SQL Server) 使用 CONVERT 将数据转换为类型化的 XML。These examples show use of CONVERT to convert data to typed XML, by using the XML Data Type and Columns (SQL Server).

此示例将包含空格、文本和标记的字符串转换为类型化的 XML,并删除所有无用空格(节点之间的边界空格):This example converts a string with white space, text and markup into typed XML, and removes all insignificant white space (boundary white space between nodes):

SELECT CONVERT(XML, '<root><child/></root>')  

此示例将包含空格、文本和标记的类似字符串转换为类型化的 XML,并保留无用空格(节点之间的边界空格):This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):

SELECT CONVERT(XML, '<root>          <child/>         </root>', 1)  

此示例将包含空格、文本和标记的字符串转换为类型化的 XML:This example casts a string with white space, text, and markup into typed XML:

SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)  

有关更多示例,请参阅创建 XML 数据的实例See Create Instances of XML Data for more examples.

G.G. 对 datetime 数据使用 CAST 和 CONVERTUsing CAST and CONVERT with datetime data

GETDATE() 值开始,此示例显示当前日期和时间,使用 CAST 将当前日期和时间更改为字符数据类型,然后使用 CONVERTISO 8601 格式显示日期和时间。Starting with GETDATE() values, this example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT to display the date and time in the ISO 8601 format.

SELECT   
   GETDATE() AS UnconvertedDateTime,  
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,  
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;  
GO  

下面是结果集:Here is the result set.

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)  

此示例与上述示例几乎完全相反。This example is approximately the opposite of the previous example. 该示例将日期和时间显示为字符数据,使用 CAST 将字符数据更改为 datetime 数据类型,然后使用 CONVERT 将字符数据更改为 datetime 数据类型。This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.

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  

下面是结果集:Here is the result set.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997

(1 row(s) affected)  

H.H. 使用 CONVERT 处理二进制和字符数据Using CONVERT with binary and character data

这些示例使用不同样式显示二进制和字符数据转换的结果。These examples show the results of binary and character data conversion, using different styles.

--Convert the binary value 0x4E616d65 to a character value.  
SELECT CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];  

下面是结果集:Here is the result set.

Style 0, binary to character
----------------------------
Name  

(1 row(s) affected)  

此示例显示 Style 1 可以强制截断结果。This example shows that Style 1 can force result truncation. 结果集中的字符 0x 强制实施截断。The characters 0x in the result set force the truncation.

SELECT CONVERT(char(8), 0x4E616d65, 1) AS [Style 1, binary to character];  

下面是结果集:Here is the result set.

Style 1, binary to character
------------------------------
0x4E616D

(1 row(s) affected)  

该例显示 Style 2 不截断结果,因为结果中不包括字符 0x。This example shows that Style 2 does not truncate the result, because the result does not include the characters 0x.

SELECT CONVERT(char(8), 0x4E616d65, 2) AS [Style 2, binary to character];  

下面是结果集:Here is the result set.

Style 2, binary to character
------------------------------
4E616D65

(1 row(s) affected)  

将字符值“Name”转换为二进制值。Convert the character value 'Name' to a binary value.

SELECT CONVERT(binary(8), 'Name', 0) AS [Style 0, character to binary];  

下面是结果集:Here is the result set.

Style 0, character to binary
----------------------------
0x4E616D6500000000

(1 row(s) affected)  
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS [Style 1, character to binary];  

下面是结果集:Here is the result set.

Style 1, character to binary
---------------------------- 
0x4E616D65

(1 row(s) affected)  
SELECT CONVERT(binary(4), '4E616D65', 2) AS [Style 2, character to binary];  

下面是结果集:Here is the result set.

Style 2, character to binary  
----------------------------------  
0x4E616D65

(1 row(s) affected)  

I.I. 转换日期和时间数据类型Converting date and time data types

此示例显示了日期、时间及日期/时间数据类型的转换。This example shows the conversion of date, time, and datetime data types.

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

J.J. 使用 CONVERT 处理不同格式的 datetime 数据Using CONVERT with datetime data in different formats

GETDATE() 值开始,此示例使用CONVERT 显示本文日期和时间样式部分的所有日期和时间样式。Starting with GETDATE() values, this example uses CONVERT to display of all the date and time styles in section Date and Time styles of this article.

格式编号Format # 示例查询Example query 示例结果Sample result
00 SELECT CONVERT(nvarchar, GETDATE(), 0) Aug 23 2019 1:39PMAug 23 2019 1:39PM
11 SELECT CONVERT(nvarchar, GETDATE(), 1) 08/23/1908/23/19
22 SELECT CONVERT(nvarchar, GETDATE(), 2) 19.08.2319.08.23
33 SELECT CONVERT(nvarchar, GETDATE(), 3) 23/08/1923/08/19
44 SELECT CONVERT(nvarchar, GETDATE(), 4) 23.08.1923.08.19
55 SELECT CONVERT(nvarchar, GETDATE(), 5) 23-08-1923-08-19
66 SELECT CONVERT(nvarchar, GETDATE(), 6) 23 Aug 1923 Aug 19
77 SELECT CONVERT(nvarchar, GETDATE(), 7) Aug 23, 19Aug 23, 19
8、24 或 1088 or 24 or 108 SELECT CONVERT(nvarchar, GETDATE(), 8) 13:39:1713:39:17
9 或 1099 or 109 SELECT CONVERT(nvarchar, GETDATE(), 9) Aug 23 2019 1:39:17:090PMAug 23 2019 1:39:17:090PM
1010 SELECT CONVERT(nvarchar, GETDATE(), 10) 08-23-1908-23-19
1111 SELECT CONVERT(nvarchar, GETDATE(), 11) 19/08/2319/08/23
1212 SELECT CONVERT(nvarchar, GETDATE(), 12) 190823190823
13 或 11313 or 113 SELECT CONVERT(nvarchar, GETDATE(), 13) 23 Aug 2019 13:39:17:09023 Aug 2019 13:39:17:090
14 或 11414 or 114 SELECT CONVERT(nvarchar, GETDATE(), 14) 13:39:17:09013:39:17:090
20 或 12020 or 120 SELECT CONVERT(nvarchar, GETDATE(), 20) 2019-08-23 13:39:172019-08-23 13:39:17
21、25 或 12121 or 25 or 121 SELECT CONVERT(nvarchar, GETDATE(), 21) 2019-08-23 13:39:17.0902019-08-23 13:39:17.090
2222 SELECT CONVERT(nvarchar, GETDATE(), 22) 08/23/19 1:39:17 PM08/23/19 1:39:17 PM
2323 SELECT CONVERT(nvarchar, GETDATE(), 23) 2019-08-232019-08-23
101101 SELECT CONVERT(nvarchar, GETDATE(), 101) 08/23/201908/23/2019
102102 SELECT CONVERT(nvarchar, GETDATE(), 102) 2019.08.232019.08.23
103103 SELECT CONVERT(nvarchar, GETDATE(), 103) 23/08/201923/08/2019
104104 SELECT CONVERT(nvarchar, GETDATE(), 104) 23.08.201923.08.2019
105105 SELECT CONVERT(nvarchar, GETDATE(), 105) 23-08-201923-08-2019
106106 SELECT CONVERT(nvarchar, GETDATE(), 106) 23 Aug 201923 Aug 2019
107107 SELECT CONVERT(nvarchar, GETDATE(), 107) Aug 23, 2019Aug 23, 2019
110110 SELECT CONVERT(nvarchar, GETDATE(), 110) 08-23-201908-23-2019
111111 SELECT CONVERT(nvarchar, GETDATE(), 111) 2019/08/232019/08/23
112112 SELECT CONVERT(nvarchar, GETDATE(), 112) 2019082320190823
113113 SELECT CONVERT(nvarchar, GETDATE(), 113) 23 Aug 2019 13:39:17.09023 Aug 2019 13:39:17.090
120120 SELECT CONVERT(nvarchar, GETDATE(), 120) 2019-08-23 13:39:172019-08-23 13:39:17
121121 SELECT CONVERT(nvarchar, GETDATE(), 121) 2019-08-23 13:39:17.0902019-08-23 13:39:17.090
126126 SELECT CONVERT(nvarchar, GETDATE(), 126) 2019-08-23T13:39:17.0902019-08-23T13:39:17.090
127127 SELECT CONVERT(nvarchar, GETDATE(), 127) 2019-08-23T13:39:17.0902019-08-23T13:39:17.090
130130 SELECT CONVERT(nvarchar, GETDATE(), 130) 22 ذو الحجة 1440 1:39:17.090P22 ذو الحجة 1440 1:39:17.090P
131131 SELECT CONVERT(nvarchar, GETDATE(), 131) 22/12/1440 1:39:17.090PM22/12/1440 1:39:17.090PM

K.K. 允许的转换中数据类型优先级的影响Effects of data type precedence in allowed conversions

以下示例定义一个类型为 VARCHAR 的变量,将整数值赋给该变量,然后选择该变量与字符串的串联。The following example defines a variable of type VARCHAR, assigns an integer value to the variable, then selects a concatenation of the variable with a string.

DECLARE @string varchar(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result

下面是结果集:Here is the result set.

Result
-----------------------
1 is a string.

int 值 1 已转换为 VARCHAR。The int value of 1 was converted to a VARCHAR.

此示例显示了一个类似的查询,但它使用的是 int 变量:This example shows a similar query, using an int variable instead:

DECLARE @notastring int;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result

在此例中,SELECT 语句会引发以下错误:In this case, the SELECT statement will throw the following error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.

为了计算表达式 @notastring + ' is not a string.'SQL ServerSQL Server 需要先遵循数据类型优先级的规则来完成隐式转换,然后才能计算表达式的结果。In order to evaluate the expression @notastring + ' is not a string.', SQL ServerSQL Server needs to follow the rules of data type precedence to complete the implicit conversion before the result of the expression can be calculated. 由于 int 的优先级高于 VARCHAR,SQL ServerSQL Server 会尝试将字符串转换为整数,但是会失败,因为此字符串无法转换为整数。Because int has a higher precedence than VARCHAR, SQL ServerSQL Server attempts to convert the string to an integer and fails because this string cannot be converted to an integer.

如果我们提供可转换的字符串,则该语句将成功,如以下示例中所示:If we provide a string that can be converted, the statement will succeed, as seen in the following example:

DECLARE @notastring int;
SET @notastring = '1';
SELECT @notastring + '1'

在此例中,字符串 '1' 可以转换为整数值 1,因而此 SELECT 语句会返回值 2。In this case, the string '1' can be converted to the integer value 1, so this SELECT statement will return the value 2. 当提供的数据类型为整数时,+ 运算符会成为加法运算符而不是字符串串联。When the data types provided are integers, the + operator becomes addition mathematical operator, rather than a string concatenation.

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

L.L. 使用 CAST 和 CONVERTUsing CAST and CONVERT

此示例检索标价的第一位是 3 的产品的名称,并将这些产品的 ListPrice 转换为 int 。它使用 AdventureWorksDW2016 数据库。This example retrieves the name of the product for those products that have a 3 in the first digit of their list price, and converts the ListPrice of these products to int. It uses the AdventureWorksDW2016 database.

SELECT EnglishProductName AS ProductName, ListPrice  
FROM dbo.DimProduct  
WHERE CAST(ListPrice AS int) LIKE '3%';  

此示例显示使用 CONVERT 而不是 CAST 的相同查询。This example shows the same query, using CONVERT instead of CAST. 它使用 AdventureWorksDW2016 数据库。It uses the AdventureWorksDW2016 database.

SELECT EnglishProductName AS ProductName, ListPrice  
FROM dbo.DimProduct  
WHERE CONVERT(int, ListPrice) LIKE '3%';  

M.M. 将 CAST 与算术运算符结合使用Using CAST with arithmetic operators

此例通过产品单价 (UnitPrice) 除以折扣率 (UnitPriceDiscountPct) 计算单列值。This example calculates a single column value by dividing the product unit price (UnitPrice) by the discount percentage (UnitPriceDiscountPct). 然后,此结果会舍入到最接近的整数,并最终转换为 int 数据类型。This result is then rounded to the nearest whole number, and finally converted to an int data type. 此示例使用 AdventureWorksDW2016 数据库。This example uses the AdventureWorksDW2016 database.

SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,  
       CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice  
FROM dbo.FactResellerSales  
WHERE SalesOrderNumber = 'SO47355'   
      AND UnitPriceDiscountPct > .02;  

下面是结果集:Here is the result set.

ProductKey  UnitPrice  UnitPriceDiscountPct  DiscountPrice
----------  ---------  --------------------  -------------
323         430.6445   0.05                  22
213         18.5043    0.05                  1
456         37.4950    0.10                  4
456         37.4950    0.10                  4
216         18.5043    0.05                  1  

N.N. 将 CAST 与 LIKE 子句一起作用Using CAST with the LIKE clause

此例将 money 列 ListPrice 转换为 int 类型,然后转换为 char(20) 类型,以便 LIKE 子句可以使用它 。This example converts the money column ListPrice to an int type, and then to a char(20) type, so that the LIKE clause can use it. 此示例使用 AdventureWorksDW2016 数据库。This example uses the AdventureWorksDW2016 database.

SELECT EnglishProductName AS Name, ListPrice  
FROM dbo.DimProduct  
WHERE CAST(CAST(ListPrice AS int) AS char(20)) LIKE '2%';  

O.O. 对 datetime 数据使用 CAST 和 CONVERTUsing CAST and CONVERT with datetime data

此示例显示当前日期和时间,使用 CAST 将当前日期和时间更改为字符数据类型,最终使用 CONVERT 以 ISO 8601 格式显示日期和时间。This example displays the current date and time, uses CAST to change the current date and time to a character data type, and finally uses CONVERT display the date and time in the ISO 8601 format. 此示例使用 AdventureWorksDW2016 数据库。This example uses the AdventureWorksDW2016 database.

SELECT TOP(1)  
   SYSDATETIME() AS UnconvertedDateTime,  
   CAST(SYSDATETIME() AS nvarchar(30)) AS UsingCast,  
   CONVERT(nvarchar(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601  
FROM dbo.DimCustomer;  

下面是结果集:Here is the result set.

UnconvertedDateTime     UsingCast                     UsingConvertTo_ISO8601  
---------------------   ---------------------------   ---------------------------  
07/20/2010 1:44:31 PM   2010-07-20 13:44:31.5879025   2010-07-20T13:44:31.5879025  

此示例与上述示例大致相反。This example is the rough opposite of the previous example. 此示例将日期和时间显示为字符数据,使用 CAST 将字符数据更改为 datetime 数据类型,然后使用 CONVERT 将字符数据更改为 datetime 数据类型 。This example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type. 此示例使用 AdventureWorksDW2016 数据库。This example uses the AdventureWorksDW2016 database.

SELECT TOP(1)   
   '2010-07-25T13:50:38.544' AS UnconvertedText,  
CAST('2010-07-25T13:50:38.544' AS datetime) AS UsingCast,  
   CONVERT(datetime, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601  
FROM dbo.DimCustomer;  

下面是结果集:Here is the result set.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM   07/25/2010 1:50:38 PM  

另请参阅See also

数据类型优先级 (Transact-SQL) Data type precedence (Transact-SQL)
数据类型转换(数据库引擎) Data Type Conversion (Database Engine)
格式 (Transact-SQL) FORMAT (Transact-SQL)
STR (Transact-SQL) STR (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
System Functions (Transact-SQL) System Functions (Transact-SQL)
排序规则和 Unicode 支持 Collation and Unicode Support
编写国际化 Transact-SQL 语句Write International Transact-SQL Statements