SQL Server 内置转换函数介绍 - 技术翻译

使用 Cast 和 Convert 转换函数从一种数据类型转换成另一种

在以前的文章中，我谈到过数据类型及其在 SQL Server 中的角色。SQL Server 使用数据类型是有一些原因的，其中一种就是协助计算表达式。但是并非所有的数据都使用了正确的数据类型。在这些情况下，在一个计算结果被确定之前所用到的一个或者更多的值必须转换成一种常见的类型。

日期既可以显式又可以隐式地从一种类型转换到另一种。 隐式的 数据类型转换是自然而然发生的；反之， 显式的 数据类型转换则会在你使用 CAST 和 CONVERT 转换函数时发生。

隐式数据转换

隐式转换是指不使用 CAST 或 CONVERT 函数所自然而然发生的那些转换。并非所有的值都可以隐式地转换成另一种类型。下面的图表显示了可以为我们之前提到过的常用数据类型进行隐式转换的类型：

允许隐式数据类型转换（这是上面图表的说明，应该放到上一段）

请注意这个图表显式的只是可能可以进行隐式转换的类型，其并不能保证一种类型中所有的值都一定可以转换成另一种。比如，VARCHAR 类型的值 'The car broke down’永远无法转换成一种正确的 DATETIME 类型。

以下为当我们使用百分比以及 INT 数据类型时发生隐式数据转换的一个例子。

在这个例子中，我们用标准成本（Standard cost）乘以数量（Quantity）。一个是 SMALLINT 值，它没有小数位，而另一个是 MONEY 类型。

SELECT P.Name,
       I.Quantity,
       P.StandardCost,
       I.Quantity * P.StandardCost as TotalCost
FROM   Production.ProductInventory I
       INNER JOIN Production.Product P
       ON P.ProductID = I.ProductID
WHERE  P.StandardCost > 0.00

当你查看结果时你会发现总成本（total cost）是有小数位的吗？ Results of Implicit Conversion

隐式转换结果（上图的说明）

在这种情况下，在 TotalCost 被计算出之前先将 Quantity 转换成了一个 MONEY 数据类型。

你可能会奇怪，为什么是 Quantity 转换成了 MONEY 类型，而不是 StandardCost 转换成 SMALLINT。

其原因是，将值从一种数据类型转换成另一种的顺序是通过类型优先级来决定的。

数据类型优先级决定了隐式数据类型转换发生时的方向或顺序。以下为我们之前提到的常见数据类型的优先级顺序：

DATETIME （最高级）
FLOAT
DECIMAL
INT
BIT
NVARCHAR
VARCHAR （最低级）

注：有超过 30 种以上被支持的数据类型，官方的列表更长。

较低优先级的数据类型将会尝试转换为更高优先级的类型，而不是从相反方向转换。

思考

SELECT 100 * .5

这个语句返回的值是 50.0。其原因是 .5 是一个较高优先级的值，因此 SQL 会将一个 INT 类型的值 100 转换成较高的优先级。由于从 100 转换为 100.00 是一个被允许的隐式转换，这个过程不会发生任何错误。

现在来思考

SELECT 'Today is ' + GETDATE()

我们试图取得一个类似于 ‘Today is 2015-07-02 08:01:54.932’ 这样的结果，但该语句返回了以下错误：

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

你可能会认为，这个语句返回一个将当前日期和时间转换为一个文本值的结果并不会很难，但由于 DATETIME 比文本值的优先级更高，所以 SQL 试图将一个文本值转换为一个日期。

‘Today is ‘ 不是一个有效的日期，SQL 不能正确从日历中识别它，因此引发了一个错误。

有迹象表明，一个值是否可以隐式的转换为一个数据类型实际上是由于两个因素决定的：

一个值是否可以从一种数据类型转换为另一种？隐式转换并不支持所有的数据类型，因此需要去了解哪些组合可以正确的工作，最重要的是理解其原理。
数据类型优先级是什么？如果一个值的数据类型已经处于较高的优先级，它不会被隐式转换为较低优先级的类型。

使用 CAST 和 CONVERT 进行显示地类型转换

在一个表达式中并不能总是将各种数据类型结合在一起使用而不导致任何错误。

当发生这种情况时，我们需要显式地将一种数据类型转换为另一种，以避免错误。

以下为一个处理日期的例子。假设我们想要查询返回有关员工生日的文本。

如果我们试图执行以下命令，将会失败：

SELECT P.FirstName + ' ' + P.LastName,
       'Birth Date ' + E.BirthDate
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

这将返回一个错误：

Msg 402, Level 16, State 1, Line 1 The data types varchar and date are incompatible in the add operator.

问题是由于这个表达式引起的

'Birth Date'+ E.BirthDate

BirthDate 字段的数据类型是 DateTime。我们要让文本与日期结合工作。为了解决这个问题，我们必须首先将 DateTime 数据类型转换为一个文本值。

我们可以使用 CAST 语句将 BirthDate 转换为一个 VARCHAR 值。这条语句执行后不会报任何错误

SELECT P.FirstName + ' ' + P.LastName,
       'Birth Date ' + CAST(E.BirthDate as VARCHAR)
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

我们可以使用 CAST 和 CONVERT 两个命令做到这一点。

CAST 和 CONVERT 之间的主要区别是，CONVERT 还允许你定义转换后的值的格式。例如，将 DATETIME 值转换为 VARCHAR 的时候非常方便。你可以将日期转换为一个更具可读性的格式。我们将在下面的部分更多采取这种方式。

现在你只是看到了我们如何使用 CAST 将值从一种数据类型转换为另一种的。下面让我们来进一步的探讨。

CAST 函数被用来将值从一种数据类型的转换为另一种的。其是一个遵守 ANSI SQL-92 标准的函数。

该函数的语法是

CAST( value as datatype )

其中 value 是要转换的项，datatype 是你想要将 value 转换为的类型。

上文中的示例 CAST(E.BirthDate as VARCHAR) 将 DATETIME 类型的字段 BIrthDate 转换为了一个 VARCHAR 文本值。

当从一种数据类型的值转换为一个 VARCHAR 时我通常不使用 CAST，因为我通常需要对值进行格式化；然而，我在只需要进行转换时才使用 CAST。这种情况下我想要：

将一个 VARCHAR 或其他文本值转换为一个可以进行计算的数字或 DATETIME 值，
需要将数值转换为同一种类型，比如当 INT 与 FLOAT 结合使用时。

思考一下这个例子。产品经理想要将手头上的库存数量减少 10%。新的总额为多少？

我们开始使用以下查询语句

SELECT   I.ProductID,
         I.LocationID,
         I.Shelf,
         I.Bin,
         I.Quantity,
         I.Quantity * .90 as ReducedQuantity
FROM     Production.ProductInventory I
ORDER BY I.ProductID, I.LocationID

你将以下结果递交给产品经理

带有小数位的减少后的数量（上图的说明）

但请注意 ReducedQuantity 列。带有小数位！产品经理会抱怨这一点的。显而易见，她会使用愤怒的语气来嘲讽你，我们的库存中不可能有半辆自行车，那么为什么报表种会这样显式？

该怎么办呢？使用 CAST 来将计算的值转换回一个整数。

我们可以使用下面的查询语句来做到这点

SELECT   I.ProductID,
         I.LocationID,
         I.Shelf,
         I.Bin,
         I.Quantity,
         CAST(I.Quantity * .90 as SMALLINT) as ReducedQuantity
FROM     Production.ProductInventory IORDER BY I.ProductID, I.LocationID

你将下列结果发送给认为他们可以接受的管理者：

减少数量后的结果集 – 消除了小数位

在这个例子中，我使用将 quantity 转换为 SMALLINT 的方式将这一列的数据类型变成了 SMALLINT，我也可以将其转换为 INT，不过我想要保持类型一致。

CONVERT

CONVERT 函数被用来将一个值从一种数据类型转换为另一种，并可以指定其格式。其是 SQL Server 特有的，而不是一个遵守 ANSI SQL-92 的函数。

该函数的语法如下

CONVERT( datatype , value , style )

说明

datatype 是你想要将 value 转换为的类型
value 是你想要转换的项
style 是你想要得到的转换后的值的格式。

CONVERT 函数真正的亮点在于你想要显式日期或数值的文本时。我们来说一下日期的转换。只要你学会了其诀窍，那么数值的转换也不在话下。

转换 DATETIME 类型
思考下列语句：

SELECT 'Today''s date is ' + CAST(GETDATE() as varchar)

返回了结果 Today’s date is Jul 4 2015 10:35AM 有三件事情需要注意：

转换函数 DATETIME 的格式

提示: 你会注意到有两种格式存在。显示日期时你可以选择显示或不显示世纪。单数字格式只显示两位数的年（yy）；用四们数字世纪（yyyy)。

记住：格式是转化的可选项。

总结

CAST 和 CONVERT 都是用来将一种类型的数值转换成另一种类型。CAST 遵循 ANSI SQL-92 标准，所以如果你需要在不同的数据库执行，如 Oracle，Mysql，SQL Server，你写 sql 时最好使用 CAST。

CONVERT 不遵循 ANSI SQL-92 标准。它的好处是你在转换数值时可以指定格式。这很有用，特别是你处理日期数值，将数值转换成 text。

需要遵循 SQL-92 写代码时请使用 CAST，需要更多的格式请使用 .CONVERT。

文章 Introduction to SQL Server’s Built-In Conversion Functions 首发于 Essential SQL。

本文地址： https://www.oschina.net/translate/introduction-to-sql-server-s-built-in-conversion-f

原文地址： http://www.codeproject.com/Articles/1068739/Introduction-to-SQL-Server-s-Built-In-Conversion-F