T-SQL中的格式转换,CAST和CONVERT

在 SQL Server 2012 中将表达式由一种数据类型转换为另一种数据类型。

语法

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

参数

expression

任何有效的表达式。

data_type

目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。

length

指定目标数据类型长度的可选整数。默认值为 30。

style

指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL,则返回 NULL。该范围是由 data_type 确定的。有关详细信息,请参阅“备注”部分。

返回类型

返回转换为 data_type 的 expression。

备注

Date 和 Time 样式

如果 expression 为 date 或 time 数据类型,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3)
01001、2 默认 mon dd yyyy hh:miAM(或 PM)
1 101 美国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yyyy
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
91091、2 默认格式 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd

yyyymmdd

131131、2 欧洲默认格式 + 毫秒 dd mon yyyy hh:mi:ss:mmm(24h)
14 114 hh:mi:ss:mmm(24h)
20120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss(24h)
21121 (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 默认值(style010091091311320120 以及 21121)始终返回世纪数位 (yyyy)。

3 转换为 datetime 时输入;转换为字符数据时输出。

4 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换,其输出格式如上一个表所述。

5 回历是有多种变体的日历系统。SQL Server 使用科威特算法。

重要提示
默认情况下,SQL Server 基于截止年份 2049 年来解释两位数的年份。换言之,就是将两位数的年份 49 解释为 2049,将两位数的年份 50 解释为 1950。许多客户端应用程序(如基于自动化对象的应用程序)都使用截止年份 2030 年。SQL Server 提供了 two digit year cutoff 配置选项以更改 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 值转换时,可截断不需要的日期部分。

从样式包含时间的字符数据转换为 datetimeoffset 时,将在结果末尾追加时区偏移量。

float 和 real 样式

如果 expression 为 float 或 real,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

输出
0(默认值) 最多包含 6 位。根据需要使用科学记数法。
1 始终为 8 位值。始终使用科学记数法。
2 始终为 16 位值。始终使用科学记数法。
126, 128, 129 为了保持向后兼容而包括在内,在以后的版本中可能不推荐使用。

money 和 smallmoney 样式

如果 expression 为 money 或 smallmoney,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

输出
0(默认值) 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取两位数,例如 4235.98。
1 小数点左侧每三位数字之间以逗号分隔,小数点右侧取两位数,例如 3,510.92。
2 小数点左侧每三位数字之间不以逗号分隔,小数点右侧取四位数,例如 4235.9819。
126 转换为 char(n) 或 varchar(n) 时,等同于样式 2

xml 样式

如果 expression 为 xml,则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

输出
0(默认值) 使用默认的分析行为,即放弃无用的空格,且不允许使用内部 DTD 子集。

注意
转换为 xml 数据类型时,SQL Server 的无用空格处理方式不同于 XML 1.0。有关详细信息,请参阅创建 XML 数据的实例。
1 保留无用空格。此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space=”preserve” 的行为相同。
2 启用有限的内部 DTD 子集处理。

如果启用,则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。

  • 应用属性的默认值。
  • 解析并扩展内部实体引用。
  • 检查 DTD 内容模型以实现语法的正确性。

分析器将忽略外部 DTD 子集。此外,不评估 XML 声明来查看 standalone 属性是设置为 yes 还是 no,而是将 XML 实例当成一个独立文档进行分析。

3 保留无用空格,并启用有限的内部 DTD 子集处理。

二进制样式

如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n),则 style 可以为下表中显示的值之一。表中没有列出的样式值将返回错误。

输出
0(默认值) 将 ASCII 字符转换为二进制字节,或者将二进制字节转换为 ASCII 字符。每个字符或字节按照 1:1 进行转换。

如果 data_type 为二进制类型,则会在结果左侧添加字符 0x。

1, 2 如果 data_type 为二进制类型,则表达式必须为字符表达式。 expression 必须由数量为偶数的十六进制数字(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 必须为表达式中的前两个字符。如果表达式中包含的字符数为奇数或者包含任何无效的字符,则会引发错误。

如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

如果固定长度 data_types 大于转换后的结果,则会在结果右侧添加零。

如果 data_type 为字符类型,则表达式必须为二进制表达式。每个二进制字符均转换为两个十六进制字符。如果转换后的表达式长度大于 data_type 长度,则会在右侧截断结果。

如果 data_type 为固定大小的字符类型,并且转换后的结果长度小于其 data_type 长度,则会在转换后的表达式右侧添加空格,以使十六进制数字的个数保持为偶数。

对于 style 1,将在转换后的结果左侧添加字符 0x。