SQL数据转换

目录

隐式数据转换

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

CAST

CONVERT

转换日期时间类型

概括

隐式转换是在不使用CAST或CONVERT函数时自动发生的转换。并非所有值都可以隐式转换为另一种数据类型。下图显示了我们之前介绍的常见数据类型可以隐式转换的内容：

SQL Conversion Functions – 允许的隐式数据类型转换。

请记住，此图表显示了可能的隐式转换。它不保证一种类型的所有值都会转换为另一种类型。例如， “汽车坏了”这个VARCHAR值永远不会产生好结果DATETIME。

当我们使用百分比和INT数据类型时，会出现一个隐式数据转换的例子。

在这个例子中，我们用Quantity乘以StandardCost。一个是没有小数位的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

当您查看结果时，您会看到总成本有小数位吗？

隐式转换结果

在这种情况下，在计算TotalCost之前，首先将Quantity转换为MONEY数据类型。

您可能想知道为什么Quantity转换为Money而不是StandardCost转换为SMALLINT。

原因是顺序值从一种数据类型隐式转换为另一种数据类型是由类型优先级决定的。

数据类型优先级决定了隐式数据类型发生的方向或顺序。

注意：支持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.

'Today is'不是有效日期，SQL不知道该日期在任何日历上的位置，因此会引发错误。

确实有两个因素可以确定项目是否可以隐式转换为数据类型：

1. 值可以从一种数据类型转换为另一种数据类型吗？并非所有数据类型都支持隐式转换，因此了解某些组合是否有效很重要。
2. 什么是数据类型优先级？如果值的数据类型已经处于较高优先级，则不会隐式转换为较低优先级。

在一个表达式中将数据类型组合在一起而不导致服务器错误并不总是可能的。

发生这种情况时，我们需要将其中一种数据类型显式转换为另一种以避免错误，这可以使用其中一个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将值从一种数据类型转换为另一种数据类型。让我们进一步探索。

SQL CAST转换函数用于将值从一种数据类型转换为另一种数据类型。它是一个符合ANSI SQL-92的函数。

该函数的语法是：

CAST(value as datatype)

在上一节的示例中，CAST(E.BirthDate as VARCHAR)将DATETIME列BirthDate转换为VARCHAR文本值。

从一个DATATYPE值转换为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

您将这些结果交给生产经理：

 SQL 转换函数——带小数位的减少数量

但请注意该ReducedQuantity列。有小数位！生产经理对此抱怨。她以恼怒的语气向您提到，很明显我们的库存中没有½辆自行车，那么为什么报告显示它们！

该怎么办？用于CAST将计算值转换回整数。

我们可以使用以下查询来做到这一点：

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

您将以下结果交给认为可以接受的经理：

减少数量的结果——消除小数值

在此示例中，由于列数据类型为SMALLINT，我过去将数量转换为SMALLINT，我可以将其强制转换为INT，但希望保持类型一致。

SQL CONVERT转换函数用于通过格式化将值从一种数据类型转换为另一种数据类型。它特定于SQL Server，而不是符合ANSI SQL-92的函数。

该函数的语法是：

CONVERT(datatype, value, style)

• 数据类型是将值转换为的类型
• value是您要转换的项
• style是您看到转换后的值的格式。

当您想要在文本中显示日期或数值时，该CONVERT函数真正发挥作用的地方。我们将专注于转换日期。一旦掌握了这一点，那么转换数值就很容易了。

考虑以下：

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

返回结果。

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

1. 我使用两个单引号在文本值中嵌入单引号。这称为“转义”字符（请参阅常量，Transact-SQL）。我们需要这样做，以便SQL不认为单引号表示文本值的结束。
2. GETDATE()函数用于返回当前日期。
3. 日期不是最适合阅读的格式。

那么我们如何修复格式呢？我们可以使用CONVERT一种风格。

查看：

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

返回结果。

今天的日期是07/04/2015

好多了！

CONVERT中的“101”是样式。MSDN上列出了更多样式，但一些比较流行的用于日期的样式是：

CONVERT函数DATETIME样式

注意：您会注意到每个列表有两种样式。这样您就可以选择显示带或不带世纪的日期。一位数样式将年份显示为两位数(yy)；而数百年的年份显示为四位数（yyyy）。

请记住，convert样式是可选的。

CAST和CONVERT两者都用于将值从一种数据类型切换到另一种数据类型。CAST符合ANSI SQL-92标准，因此当您要编写需要在不同DBMS（如Oracle、MySQL 或SQL Server）上执行的SQL时非常有用。

CONVERT不符合ANSI SQL-92。它的优点是您可以指定有助于格式化转换值的样式。这些很方便，尤其是在处理日期时，将值转换为文本时。

如果您希望Transact-SQL程序代码符合SQL-92，请使用CAST而不是CONVERT。使用CONVERT而不是CAST来利用CONVERT中的样式功能。

本文最初发布于SQL Data Conversions - Essential SQL

https://www.codeproject.com/Articles/5326763/SQL-Data-Conversions