SQL Server中日期/时间值到字符类型的数据转换

SQL Server中日期/时间值到字符类型的数据转换 

2009-07-22 11:20:34|  分类： 专业技术 |  标签： |字号大中小 订阅

  本文的前提是假定你已具备T-SQL和SQL Server应用知识，并且该部分只涉及日期/时间数据与字符数据之间的相互转换。但是，你还可以转换其它类型的数值，如将INT转换为DATETIME。虽然在大多数情况下，你的主要工作是字符到日期/时间的转换。

  隐式转换数据

  当你插入数据到DATETIME或SMALLDATETIME字段中时，SQL Server会自动尝试将不同类型的数据进行转换。例如，如果你向DATETIME字段中插入CHAR值，SQL Server将对数据作转换——如果该值是一个可以接受的格式。如果你在CHAR栏中插入DATETIME值，SQL Server也将作自动转换。

  让我们来看看几个隐式转换例子以便更好地理解它是如何工作的。为了说明这些转换，我使用了下面的代码在AdventureWorks示例数据库中创建LogInfo表：

USE AdventureWorks
GO
CREATE TABLE dbo.LogInfo 
(
LogID INT PRIMARY KEY ， 
LogEvent NVARCHAR ( 30 ) NOT NULL ， 
Post_DateTime DATETIME NOT NULL ， 
Post_SmallDateTime SMALLDATETIME NOT NULL ， 
Post_NVarChar NVARCHAR ( 25 ) NOT NULL
)

  在这个表中包含了三个用于保存日期/时间信息的字段：Post_DateTime、Post_SmallDateTime和Post_NVarChar。字段的名称反映了用于定义字段的数据类型。下面让我们在这些字段中插入数据：

INSERT INTO LogInfo
SELECT DatabaseLogID， [ Event ] ， 
PostTime， PostTime， PostTime
FROM dbo.DatabaseLog

  这个语句将从DatabaseLog表（在AdventureWorks数据库）中获取数据，然后插入到LogInfo表中。在源表的PostTime字段是DATETIME数据类型的。注意，这个字段是用于将数据插入到LogInfo表的每个日期/时间字段的。
在你填充好表的数据后，你可以使用下面的SELECT语句来获取LogInfo表的第一行记录：

SELECT * from dbo.LogInfo
WHERE LogID = 1

  SELECT语句返回LogID值为1的记录行的所有字段的值。下面的结果显示了数据是如何存储在表中的。

LogID
LogEvent
Post_DateTime
Post_SmallDateTime < ？xml:namespace prefix = o />< ？xml:namespace prefix = o />
Post_NVarChar

1
CREATE_TABLE
2005 - 10 - 14 01 : 58 : 27.567
2005 - 10 - 14 01 : 58 : 00
Oct 14 2005 1 :58AM

  你可以看到，每个日期/时间值都稍微有些不同。The Post_DateTime字段存储的是完整日期和时间值。但是，正如我们所预期的，Post_SmallDateTime字段存储一个缩短的时间（00表示秒）。最后，Post_NVarChar存储的是一个与其它两个非常不一样的字符串值。

  默认情况下，当SQL Server将一个DATETIME或SMALLDATETIME值转换为一个字符值时，它使用上面显示的格式（Oct 14 2005 1:58AM)。在后面的文章中，你将会知道我们还可以将这种格式修改成其它的一些可用的格式。但是目前而言，我们要知道的重要一点是SQL Server是如何隐式转换日期/时间值的。现在让我们来看看SQL Server中的显式数据转换。
显式地转换日期/时间值，你必须使用CAST或CONVERT Transact-SQL方法。由于CAST方法是两者中相对简单的，因此我们从这个开始介绍。下面这个SELECT语句使用CAST方法将Post_NVarChar字段中的字符数据转换成一个DATETIME值。

SELECT LogID， LogEvent， 
CAST (Post_NVarChar AS DATETIME ) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

  当你使用CAST方法时，你必须指定源字段名称（或其它一些表达式）、AS关键字和值转换的数据类型——这里是DATETIME。当你运行这个语句时，值就被转换了，如下面显示的结果：

LogID
LogEvent
Post_Converted < ？xml:namespace prefix = o />< ？xml:namespace prefix = o />< ？xml:namespace prefix = o />< ？xml:namespace prefix = o />
1
CREATE_TABLE
2005 - 10 - 14 01 : 58 : 00.000
( 1 row(s) affected)

  注意，Post_Converted字段（别名赋给SELECT子句中的字段）预期是包括完整日期时间值并精确到毫秒的DATETIME格式。但是，秒是表示为00.000。这是因为当SQL Server转换原始值时，它会去掉秒而只存储小时和分钟值。当你将值转换回DATETIME时，SQL Server 将秒设置为00.000。

  然而，如果日期/时间值是以作为字符串存储的并使用DATETIME数据所使用的格式，那么SQL Server就会保留秒。比如，下面的SELECT语句使用CAST方法将字符串值转换为DATETIME：

SELECT CAST ( ' 2005-10-14 01:58:27.567 ' AS DATETIME ) AS [ Date/Time ]

  下面的结果显示秒和毫秒现在被保存了：

Date / Time
2005 - 10 - 14 01 : 58 : 27.567
( 1 row(s) affected)

  除了显式地将DATETIME（或SMALLDATETIME）值转换成字符数据，你也可以使用CAST方法将DATETIME数据转换成字符数据。下面的SELECT语句使用CAST功能从Post_DateTime字段中获取数据：

SELECT LogID， LogEvent， 
CAST (Post_DateTime AS VARCHAR ( 20 )) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

正如你所看到的下面显示的结果，句法将值转换成VARCHAR：

LogID
LogEvent
Post_Converted
1
CREATE_TABLE
Oct 14 2005 1 :58AM
( 1 row(s) affected)

  注意，当SQL Server隐式地将DATETIMER值转换成NVARCHA时，转换的值的格式现在就是你先前看到格式。

  现在你了解了如何使用CAST方法，那么让我们接着看看CONVERT方法。最基本的，CONVERT方法返回与CAST方法一样的结果。比如，与上面的例子一样，下面的语句将Post_DateTime值转换成VARCHAR：

SELECT LogID， LogEvent， 
CONVERT ( VARCHAR ( 20 )， Post_DateTime) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

  然而，注意在CONVERT方法中的参数的顺序与CAST方法的是不一样的。当使用CONVERT时，你首先指定目标数据类型（VARCHAR），然后是源字段(Post_DateTime)的名称，它是由逗号分隔的两个参数，而不是AS关键字。当你运行语句时，你会得到下面的结果：

LogID
LogEvent
Post_Converted
1
CREATE_TABLE
Oct 14 2005 1 :58AM
( 1 row(s) affected)

  结果跟前面的例子是一样的。但是，如果你想要以一定的格式显示你的日期/时间值，而不是使用目前我们所看到的格式(Oct 14 2005 1:58AM)。这时，你可以在CONVERT方法中添加第三个参数来指定新的格式，如下面的例子所显示的：

SELECT LogID， LogEvent， 
CONVERT ( VARCHAR ( 20 )， Post_DateTime， 101 ) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

  注意，101已经作为第三个参数添加到方法中。当指定一个格式时，你必须使用由T-SQL支持的预定义代码来表示你想要使用的格式。在这种情况下，101返回如下所显示格式的结果：

LogID
LogEvent
Post_Converted
1
CREATE_TABLE
10 / 14 / 2005
( 1 row(s) affected)

   Post_Converted值现在的格式是10/14/2005，这个也是代码101代表的格式。如果你想要你的结果显示为类似于DATETIME值所显示的格式，那么你可以指定代码121，如下面的例子：

SELECT LogID， LogEvent， 
CONVERT ( VARCHAR ( 25 )， Post_DateTime， 121 ) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

  现在返回的结果是完整日期和时间值，精确到毫秒：

LogID
LogEvent
Post_Converted
1
CREATE_TABLE
2005 - 10 - 14 01 : 58 : 27.567
( 1 row(s) affected)

T-SQL支持多种预定义的格式。关于用于调用每个格式的格式命名和代码的完整清单，你可以在Microsoft SQL Server Books Online中阅读CAST和CONVERT（Transact-SQL）专题。

  现在让我们来看一个不同的例子。在下面的SELECT语句中，我们使用了CONVERT方法将Post_SmallDateTime字段栏转换成一个VARCHAR字段：

SELECT LogID， LogEvent， 
CONVERT ( VARCHAR ( 25 )， Post_SmallDateTime， 121 ) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

  正如前面的例子，日期/时间值显示为121格式：

LogID
LogEvent
Post_Converted
1
CREATE_TABLE
2005 - 10 - 14 01 : 58 : 00.000
( 1 row(s) affected)

  注意，由于日期/时间值是从SMALLADATETIME字段中获取的，因此时间值中的秒是00.000，这与SMALLDATETIME的是一样的。以下是如何以指定更短的长度截断VARCHAR数据类型的秒：

SELECT LogID， LogEvent， 
CONVERT ( VARCHAR ( 16 )， Post_SmallDateTime， 121 ) AS Post_Converted
FROM dbo.LogInfo
WHERE LogID = 1

  目前CONVERT功能的数据类型参数显示为VARCHAR（16）而非VARCHAR（25），与前面的例子一样。下面的结果显示值是如何被截断以便秒不再显示：

LogID
LogEvent
Post_Converted
1
CREATE_TABLE
2005 - 10 - 14 01 : 58
( 1 row(s) affected)

  这就是所有关于日期/时间值的显式转换方法。当获取这些值时，CAST和CONVERT方法都是方便的工具（注意，这些方法同样可以用于转换其它类型的值）。在接下来的文章中，我将阐述如何从日期/时间字段获取特定的信息，以及如何计算日期/时间值。同时，你现在也已经掌握了如何转换这些值以及以特定格式显示它们的基本用法，这对你是非常有用的。