SQLServerCast、Convert数据类型转换
⼀、概述
本篇⽂章转载来着官⽹在线⽂档，⽂章主要介绍SQL Server数据类型转换相关语法、隐式转换、Date样式等。
语法
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：指定⽬标数据类型长度的可选整数。默认值为 30。
style：指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL，则返回 NULL。该范围是由 data_type 确定的。有关详细信息，请参阅“备注”部分。
Date 和 Time 样式
如果 expression 为 date 或 time 数据类型，则style可以为下表中显⽰的值之⼀。其他值作为 0 进⾏处理。SQL Server 使⽤科威特算法来⽀持阿拉伯样式的⽇期格式。
不带世纪数位 (yy) (1)带世纪数位 (yyyy)标准输⼊/输出 (3)
-0 或 100 (1,2)默认mon dd yyyy hh:miAM（或 PM）
1101美国mm/dd/yyyy
dd
3103英国/法国dd/mm/yyyy
4104德国
5105意⼤利dd-mm-yy
6106 (1)-dd mon yy
7107 (1)-mon dd, yy
8108-hh:mi:ss
-9 或 109 (1,2)默认设置 + 毫秒mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10110美国mm-dd-yy
11111⽇本yy/mm/dd
12112ISO yymmdd yyyymmdd
-13 或 113 (1,2)欧洲默认设置 + 毫秒dd mon yyyy hh:mi:ss:mmm(24h) 14114-hh:mi:ss:mmm(24h)
-
20 或 120 (2)ODBC 规范yyyy-mm-dd hh:mi:ss(24h)
-21 或 121 (2)ODBC 规范（带毫秒）yyyy-mm-dd hh:(24h)
-126 (4)ISO8601yyyy-mm-ddThh:（⽆空格）-127(6, 7)带时区 Z 的 ISO8601。
yyyy-mm-ddThh:Z
（⽆空格）
-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 默认值（style 0 或 100、9 或 109、1
3 或 113、20 或 120 以及 21 或 121）始终返回世纪数位 (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 样式
如果 data_type 为 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 ⼦集处理。
⼆进制样式
decimal是整数数据类型
如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n)，则 style 可以为下表中显⽰的值
之⼀。表中没有列出的样式值将返回错误。
值输出
0（默认值）将 ASCII 字符转换为⼆进制字节，或者将⼆进制字节转换为 ASCII 字符。每个字符或字节按照 1:1 进⾏转换。
如果data_type为⼆进制类型，则会在结果左侧添加字符 0x。
如果data_type为⼆进制类型，则表达式必须为字符表达式。expression必须由数量为偶数的⼗六进制数字（0、1、2、3、4、5、
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 。
隐式转换
隐式转换指那些没有指定 CAST 或 CONVERT 函数的转换。显式转换指那些需要指定 CAST 或 CONVERT 函数的转换。以下图例显⽰了可对 SQL Server 系统提供的数据类型执⾏的所有显式和隐式数据类型转换。其中包括 xml 、bigint 和 sql_variant 。不存在对 sql_variant 数据类型的赋值进⾏的隐式转换，但是存在转换为 sql_variant
的隐式转换。
在 datetimeoffset 与字符类型 char 、varchar 、nchar 和 nvarchar 之间转换时，转换后的时区偏移量部分的 HH 和 MM 都应始终为两个数字，例如 -08:00。
注意：因为 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 数据类型的信息。
⽂本和图像数据类型
不⽀持对 text 和 image 数据类型进⾏⾃动数据类型转换。可将 text 数据显式转换为字符数据，将 image 数据转换为 binary 或 varbinary，但最⼤长度是 8000 字节。如果试图进⾏不正确的转换，如将包含字母的字符表达式转换为 int，则 SQL Server 将返回错误消息。
输出排序规则
如果 CAST 或 CONVERT 的输出是字符串，并且输⼊也是字符串，则输出将与输⼊具有相同的排序规则和排序规则标签。如果输⼊不是字符串，则输出采⽤数据库的默认排序规则以及强制默认的排序规则标签。
若要为输出分配不同的排序规则，请将 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 的各个版本中都相同。
转换⼩数位数不同的数据类型时，结果值有时被截断，有时被舍⼊。下表显⽰了此⾏为。
被转换的数据类型转换到的数据类型⾏为numeric numeric舍⼊
numeric int截断
numeric money舍⼊
money int舍⼊
money int舍⼊
money numeric舍⼊
float int截断
float numeric舍⼊
float datetime舍⼊datetime int舍⼊
例如，以下转换的结果为10：
SELECT CAST(10.6496AS int)
在进⾏数据类型转换时，若⽬标数据类型的⼩数位数⼩于源数据类型的⼩数位数，则该值将被截断。例如，以下转换的结果为$10.3497：SELECT CAST(10.3496847AS money)
当⾮数字型 char、nchar、varchar 或 nvarchar 数据转换为 int、float、numeric 或 decimal 时，SQL Server 将返回错误消息。当空字符串 (" ") 转换为 numeric 或 decimal 时，SQL Server 也返回错误。
备注：⽂章转载来⾃官⽅在线⽂档。
备注：
作者：
博客：
本站点所有随笔都是原创，欢迎⼤家转载；但转载时必须注明⽂章来源，且在⽂章开头明显处给明链接，否则保留追究责任的权利。
《欢迎交流讨论》

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。