英文原文： Introduction to SQL Server’s Built-In Conversion Functions

     在 表的字段中SQL Server 使用数据类型来存储特定类型的值，比如数字、日期、或者文本。数据类型也会在函数中被使用，比如一些数学表达式。

　　但在使用数据类型时有一个问题，就是通常它们之间的相容性不是很好。而类型转换函数可以让它们更好的相容。

　　尽管有时一些类型可以自动(隐式地)转换成另外一种类型，但在其他情况下，SQL 需要提供更好的方案。所以在这些情况下，就要使用 CAST 和 CONVERT 函数来解决问题了。

　　若你对 SQL 函数还不是很熟悉，那么我会建议你先去看一看 SQL Server 内置函数介绍。

　　本教程中所有的例子均是基于 Microsoft SQL Server Management Studio 和 AdventureWorks2012 数据库的。你可以通过一些免费的工具来开始学习，参看我编写的 SQL Server 入门教程 这篇指南。帮助你以后执行Oracle，Mysql，SQL Server等能得心应手。

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

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

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

　　1.隐式数据转换

　　隐式转换是指不使用 CAST 或 CONVERT 函数所自然而然发生的那些转换。并非所有的值都可以隐式地转换成另一种类型。允许隐式数据类型转换

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

    如图1所示，可以为我们之前提到过的常用数据类型进行隐式转换的类型：

　　图1

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

　　在这个例子中，我们用标准成本(Standard cost)乘以数量(Quantity)。包括：

    （1）SMALLINT 值，它没有小数位

    （2）一个是 MONEY 类型。

    如图2所示：

　　图2

　　当你查看结果时你会发现总成本(total cost)是有小数位的吗?如图3所示

　　图3

　　图3的隐式转换结果

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

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

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

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

　　（1）DATETIME (最高级)

　　（2）FLOAT

　　（3）DECIMAL

　　（4）INT

　　（5）BIT

　　（6）NVARCHAR

　　（7）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 进行显示地类型转换

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

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

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

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

　　图4

　　这将返回一个错误：

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

　　问题是由于这个表达式引起的，如下所示

　　?1'Birth Date'+ E.BirthDate

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

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

　　图5

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

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

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

　　三、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%。新的总额为多少?

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

　　图6

　　你将图7所示的结果递交给产品经理

　　图7

　　图7中带有小数位的减少后的数量

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

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

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

　　图8

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

　　图9

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

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

　　四、CONVERT

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

　　该函数的语法如下所示

　　CONVERT(datatype, value, style)

　　说明

　　（1）datatype 是你想要将 value 转换为的类型

　　（2）value 是你想要转换的项

　　（3）style 是你想要得到的转换后的值的格式。

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

　　转换 DATETIME 类型

　　思考下面的语句：

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

　　返回了如下的结果

　　Today’s date is Jul 4 2015 10:35AM

　　有三件事情需要注意：

　　（1）我使用两个单引号来表示文本值内的单引号。这就是所谓的“逃逸”字符(参看 Constants, Transact-SQL)。 我们必须这样做来使 SQL 不认为单引号是文本值的结尾。

　　（2）GETDATE() 函数用于返回当前日期。

　　（3）返回的日期并不是适合阅读的最佳格式。

　　那么我们如何来修正这个格式呢?我们可以使用 CONVERT 并指定一个样式。

　　这样

　　SELECT 'Today''s date is ' + CONVERT(VARCHAR, GETDATE(), 101)

　　将返回如下所示的结果

　　Today’s date is 07/04/2015

　　好多了!

　　CONVERT 语句种的“101”就是样式。MSDN 上有更多样式的列表，但是在转换日期时有下列一些最流行的格式：

　　图10

　　转换函数 DATETIME 的格式

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

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

　　五、总结

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

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

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

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