数据类型转换（数据库引擎）

项目
12/15/2008

可以按以下方案转换数据类型：

当一个对象的数据移到另一个对象，或两个对象之间的数据进行比较或组合时，数据可能需要从一个对象的数据类型转换为另一个对象的数据类型。
将 Transact-SQL 结果列、返回代码或输出参数中的数据移到某个程序变量中时，必须将这些数据从 SQL Server 2005 系统数据类型转换成该变量的数据类型。

可以隐式或显式转换数据类型：

隐式转换对用户不可见。
SQL Server 会自动将数据从一种数据类型转换为另一种数据类型。例如，将 smallint 与 int 进行比较时，在比较之前 smallint 会被隐式转换为 int。
显式转换使用 CAST 或 CONVERT 函数。
CAST 和 CONVERT 函数可将值（局部变量、列或其他表达式）从一种数据类型转换为另一种数据类型。例如，以下 CAST 函数可将数值 $157.27 转换为字符串 '157.27'：
```
CAST ( $157.27 AS VARCHAR(10) )
```
如果希望 Transact-SQL 程序代码符合 SQL-92 标准，请使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的样式功能，请使用 CONVERT 而不要使用 CAST。

从一个 SQL Server 对象的数据类型转换为另一种数据类型时，不支持某些隐式和显式数据类型转换。例如，nchar 值无法被转换为 image 值。nchar 只能显式转换为 binary，而不支持隐式转换为 binary。但是，nchar 既可以显式也可以隐式转换为 nvarchar。

当处理 sql_variant 数据类型时，SQL Server 支持将其他数据类型的对象隐式转换为 sql_variant 类型。但是，SQL Server 不支持从 sql_variant 数据隐式转换为其他数据类型的对象。

有关 SQL Server 对象之间支持的转换的详细信息，请参阅 CAST 和 CONVERT (Transact-SQL)。

在应用程序变量与 SQL Server 结果集列、返回代码、参数或参数标记之间进行转换时，支持的数据类型转换由数据库 API 定义。有关详细信息，请参阅将数据移至程序变量。

数据类型转换行为

本主题中的下列几部分说明了由下面的数据类型演示的转换行为：

binary 和 varbinary 数据	float 和 real 数据
bit 数据	money 数据
字符数据	decimal 和 numeric 数据
datetime 和 smalldatetime 数据	使用 OLE 自动化存储过程转换数据类型

转换 binary 和 varbinary 数据

当数据从字符串数据类型（char、varchar、nchar、nvarchar、binary、varbinary、text、ntext 或 image）转换为不同长度的 binary 或 varbinary 数据类型时，SQL Server 将在数据的右侧填充或截断数据。从其他数据类型转换为 binary 或 varbinary 时，将在数据的左侧填充或截断数据。填充将通过使用十六进制的零来完成。

如果 binary 数据是最容易来回移动的数据，则将数据转换为 binary 和 varbinary 数据类型很有用。将任一类型的任一值转换为足够大的二进制值，然后转换回原类型时，如果两次转换都是在相同的 SQL Server 版本上进行的，将始终生成相同的值。值的二进制表示形式在不同 SQL Server 版本之间可能会有所不同。

可以将 int、smallint 和 tinyint 转换为 binary 或 varbinary，但是如果将 binary 转换回整数值，则在发生了截断的情况下此值将不同于原始整数值。例如，以下 SELECT 语句显示整数值 123456 通常被存储为二进制值 0x0001e240：

SELECT CAST( 123456 AS BINARY(4) )

但是，以下 SELECT 语句显示如果 binary 目标太小而不能保存整个值，则前导数字会被自动截断，以使该数值存储为 0xe240：

SELECT CAST( 123456 AS BINARY(2) )

以下批处理显示，这种自动截断会影响算术运算而不产生错误：

DECLARE @BinaryVariable2 BINARY(2)

SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1

SELECT CAST( @BinaryVariable2 AS INT)
GO

最终结果为 57921，而不是 123457。

注意：
不能保证在 SQL Server 各个版本之间对任一数据类型与 binary 数据类型进行转换的结果是一致的。

转换为 bit 数据

转换为 bit 会将任何非零值升为 1。

转换字符数据

如果将字符表达式转换为不同大小的字符数据类型，则对于新数据类型而言过长的值将被截断。

如果将某个字符表达式转换为不同数据类型或大小的字符表达式（例如从 char(5) 转换为 varchar(5) 或从 char(20) 转换为 char(15)），则输入值的排序规则会被分配给经过转换的值。如果将非字符表达式转换为字符数据类型，则当前数据库的默认排序规则会被分配给经过转换的值。在任意一种情况下，都可以使用 COLLATE 子句分配特定的排序规则。

注意：
char 和 varchar 数据类型支持代码页转换，但是 text 数据类型不支持。与 SQL Server 的早期版本一样，将不报告代码页转换期间的数据丢失。

要转换为近似 numeric 数据类型的字符表达式包含可选的指数符号 [一个大写或小写的字母 E 后跟可选的加号 (+) 或减号 (-)，然后再跟一个数字]。

要转换为精确 numeric 数据类型的字符表达式必须包含数字、小数点和可选的加号 (+) 或减号 (-)。将忽略前导空格。逗号分隔符（例如 123,456.00 中的千位分隔符）在字符串中禁用。

要转换为 money 或 smallmoney 数据类型的字符表达式还可以包含可选的小数点和美元符号 ($)。可以使用逗号分隔符（如在 $123,456.00 中）。

以下示例显示了如何转换数据以便于显示。此示例在执行字符串比较之前将销售数据转换为字符数据，并将当前日期转换为样式 3，dd/mm/yy。

USE AdventureWorks;
GO
SELECT SalesPersonID,
   CAST(SalesYTD AS varchar(12)),
   CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%'
GO

此示例将 uniqueidentifier 值转换为 char 数据类型。

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char'
GO

此示例将当前日期转换为样式 3，dd/mm/yy。

SELECT CONVERT(char(12), GETDATE(), 3)
GO

转换 datetime 和 smalldatetime 数据

当转换为 datetime 时，SQL Server 2005 将会拒绝它无法识别为日期的所有值。这些包括公元 1 世纪 1 月 1 日以前的日期。如果日期处于正确的范围内（从 1900 年 1 月 1 日到 2079 年 6 月 6 日），就可以将 datetime 值转换为 smalldatetime。时间值被四舍五入为最接近的分钟数。

下面的示例将 smalldatetime 和 datetime 值分别转换为 varchar 和 binary 数据类型。

DECLARE @mydate_sm smalldatetime
SET @mydate_sm = '4/05/98'

SELECT CAST(@mydate_sm AS varchar) AS SM_DATE_VARCHAR
GO

DECLARE @mydate  datetime
SET @mydate     = '4/05/98'

SELECT  CAST(@mydate AS binary) AS DATE_BINARY
GO

下面是结果集：

(1 row(s) affected)

SM_DATE_VARCHAR                
------------------------------ 
Apr  5 1998 12:00AM            

(1 row(s) affected)

DATE_BINARY                                                    
-------------------------------------------------------------- 
0x0000000000000000000000000000000000000000000000008c3000000000 

(1 row(s) affected)

转换 float 和 real 数据

如果将 float 值转换为任一整数类型，这些值将被截断。

若要将 float 或 real 转换为字符数据，使用 STR 字符串函数通常比使用 CAST( ) 更有用。这是因为 STR 能够对格式进行更严格的控制。有关详细信息，请参阅STR (Transact-SQL)和Functions (Transact-SQL)。

转换 money 数据

如果将整型数据类型转换为 money，则假设采用货币单位。例如，整数值 4 被转换为相当于 4 个货币单位的 money 值。

下面的示例将 smallmoney 和 money 值分别转换为 varchar 和 decimal 数据类型。

USE AdventureWorks;
GO
DECLARE @mymoney_sm smallmoney;
SET  @mymoney_sm = 3148.29;
SELECT  CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney    money;
SET  @mymoney    = 3148.29;
SELECT  CAST(@mymoney AS decimal)    AS 'MONEY DECIMAL';

GO

下面是结果集：

SM_MONEY VARCHAR               
------------------------------ 
3148.29                        

(1 row(s) affected)

MONEY DECIMAL          
---------------------- 
3148                   

(1 row(s) affected)

转换 decimal 和 numeric 数据

对于 decimal 和 numeric 数据类型，SQL Server 会将精度和小数位数的每个特定组合视为不同的数据类型。例如，将 decimal(5,5) 和 decimal(5,0) 视为不同的数据类型。

在 Transact-SQL 语句中，带有小数点的常量将自动转换为 numeric 数据值，而且使用必需的最小精度和小数位数。例如，常量 12.345 将被转换为精度为 5，小数位数为 3 的 numeric 值。

从 decimal 或 numeric 转换为 float 或 real 会导致精度的降低。从 int、smallint、tinyint、float、real、money 或 smallmoney 转换为 decimal 或 numeric 会导致溢出。

默认情况下，将数字转换为较低精度和小数位数的 decimal 或 numeric 值时，SQL Server 会进行舍入。但如果 SET ARITHABORT 选项为 ON，则发生溢出时，SQL Server 会产生错误。若仅降低精度和小数位数，则不会产生错误。

使用 OLE 自动化存储过程转换数据类型

由于 SQL Server 使用 Transact-SQL 数据类型，而 OLE 自动化使用 Visual Basic 数据类型，因此 OLE 自动化存储过程必须转换在两者之间转换数据。

下表说明了从 SQL Server 到 Visual Basic 的数据类型转换。

SQL Server 数据类型	Visual Basic 数据类型
char, varchar, text, nvarchar, ntext	String
decimal, numeric	String
bit	Boolean
binary, varbinary, image	一维 Byte() 数组
int	Long
smallint	Integer
tinyint	Byte
float	Double
real	Single
money, smallmoney	Currency
datetime, smalldatetime	Date
全部设置为 NULL	Variant 设为空值

除了 binary、varbinary 和 image 值以外，所有单个 SQL Server 值都被转换为单个 Visual Basic 值。这些值将被转换为 Visual Basic 中的一维 Byte() 数组。此数组的范围为 Byte(0 to length1)，其中 length 是 SQL Server binary、varbinary 或 image 值中的字节数。

以下是从 Visual Basic 数据类型到 SQL Server 数据类型的转换。

Visual Basic 数据类型	SQL Server 数据类型
Long, Integer, Byte, Boolean, Object	int
Double, Single	float
Currency	money
Date	datetime
小于或等于 4000 个字符的 String	varchar/nvarchar
大于 4000 个字符的 String	text/ntext
小于或等于 8000 字节的一维 Byte() 数组	varbinary
大于 8000 字节的一维 Byte() 数组	image

请参阅