SQL2008数据类型

SQL2008数据类型

SQL2008 数据类型

--王成辉翻译整理，转贴请注明出自微软BI开拓者 www.windbi.com
--原帖地址

引言

本文介绍了 SQL Server 2008 里发现的新时间和日期的数据类型，如 DATETIME2 、 TIME 和 DATE 。也将回顾以前版本一直沿用下来的传统的数据类型。另外对管理数据类型的 TSQL 命令如 CAST 和 CONVERT 也做了相应的说明。

CAST和CONVERT

首先来回顾 CAST 和 CONVERT 是因为这些 TSQL 函数可以允许我们更加容易地查看数据类型之间的区别。 CAST 和 CONVERT 用来更改一个值的数据类型或格式。两个函数的目的相同，但 CONVERT 有能力去更改格式。格式可以用来把数字 4431.334 更改为常用的货币风格 4,431.33 （每 3 个数字一个逗号并且小数点后保留 2 位），或者把 4 位的年 2007 更改位两位的年 07 。


CAST 的语法是 CAST(value AS new type) 。例如，我们声明一个 DATETIME 的变量，然后把它转为字符串：

DECLARE @myTime AS DATETIME;
SET @myTime = '1/1/2007';
SELECT @myTime;

结果：2007-01-01 00:00:00.000

SELECT CAST(@myTime as varchar(50));

结果：Jan 1 2007 12:00AM

相反，我们也可以把字符串转为日期：
SELECT CAST('Jan 1 2007 12:00AM' as DATETIME)

结果：2007-01-01 00:00:00.000

CONVERT 的语法是 CONVERT(newtype,value,Style) 。 Style 是可选的。前面那个 CAST 例子可以用 CONVERT 重写成下面的样子：

SELECT CONVERT(DATETIME, 'Jan 1 2007 12:00AM')
结果： 2007-01-01 00:00:00.000

Style 部分对于格式化输出结果是很便利的。为了用常用的格式 mm/dd/yy 格式来显示一个日期，仅需给 CONVERT 的 Style 添加一个值即可，如下：

DECLARE @myDate AS DATETIME;
SET @myDate = '1/1/2007';
SELECT CONVERT(varchar(50), @myDate, 1)

结果： 01/01/07

要把月份显示为字母，把 CONVERT 的 Style 的值从 1 改为 7 即可：

SELECT CONVERT(varchar(50), @myDate, 7)

结果： Jan 01, 07

Style 的完整列表可以在联机丛书的 CAST 和 CONVERT 下找到。

DATETIME

标准的 DATETIME 数据类型也带入 SQLServer2008 了。尽管类型单一，但它却有日期和时间两部分。 DATETIME 占 8 个字节，前 4 个字节存储日期，后 4 个字节存储时间。日期部分实际上是与系统内部的基准日期 1900-1-1 相差的天数。这可以通过传递一个空的字符串作为 datetime 的值，然后查看结果。下面创建一个有一个字段的表，表名是 Types ，字段名是 standardDatetime 。字段的类型是 DATETIME 。传递一个空的字符串，然后返回结果：

INSERT INTO Types (standardDateTime) VALUES('');
SELECT standardDateTime FROM Types;

结果：1900-01-01 00:00:00.000

DATETIME 类型的列的年份可以在 1753 和 9999 之间。时间部分精确到 3.33 毫秒。如果你不需要秒且你的年份在 1900 和 2097 之间，那么你可以使用 SMALLDATETIME 类型。它占的字节数是 DATETIME 的一半，且秒被四舍五入到最近的分钟，如下：

DECLARE @myDate DATETIME;
SET @myDate = '2007-10-13 12:35:29.998';
SELECT @myDate;

结果：2007-10-13 12:35:29.997

SELECT CAST(@myDate AS SMALLDATETIME);

结果：2007-10-13 12:35:00

第一个结果是标准的 DATETIME 。第二个结果使用 CAST 函数把 @myDate 转为 SMALLDATETIME 来显示。

一个常见的 DATETIME 问题是当仅用日期来选择的时候会出错。例如，如果我们往测试表里载入下面 3 个值：

INSERT INTO Types(standardDateTime)
VALUES
('2007-10-12 12:35:29.998'),
('2007-10-11'),
(GETDATE())

（ 译者注：这里的Insert语句和SQL2005及以前版本的SQLServer有所不同，SQL2008支持在一个语句里给表插入多行，就像多次执行以前版本的Insert语句一样，这个功能是SQL3标准里制定的，SQL2008实现了这个功能，的确不错。）

然后执行一个 select 语句来查找 2007-10-12 ：

SELECT standardDateTime
FROM Types
WHERE standardDateTime = '2007-10-12';

没有任何记录返回，因为时间随着一起插入了。当使用 GETDATE() 函数插入时也会发生相同的结果。我们需要和日期一起来匹配时间。如果插入的时候没有包括时间，如 ’2007-10-11’ ，那么我们选出来的结果会如下显示：

SELECT * FROM Types WHERE standardDateTime = '2007-10-11'

结果：2007-10-11 00:00:00.000

作为一个小的提示，注意以前版本的 INSERT 语句。 SQL2008现在允许在一个语句里插入多行了。

DATETIME2

DATETIME2 是 SQL2008 里新的数据类型。这个新的类型类似于标准的 DATETIME ，但精确度更高。年的范围在公园 1 年 1 月 1 日到 9999 年 12 月 31 日 。时间也可以精确到秒后面的 7 位数。时钟也是 24 小时制了。所以， 8pm 可以存储为军用时间 20:00 。声明该新类型的语法可以是 DATETIME ，或者 DATETIME(n) 。 n 是在 0 到 7 之间用来指定秒后面的小数部分。

SELECT CAST('2007-10-20 20:30:05.1234567' as DATETIME2)

结果：2007-10-20 20:30:05.1234567

SELECT CAST('2007-10-20 20:30:05.1234567' as DATETIME2(4))

结果：2007-10-20 20:30:05.1235

分开的DATE和TIME

SQL2008 里日期和时间可以分开而不是放在一起存储了。分别为 DATE 和 TIME 数据类型。 TIME 类型可以象 DATETIME2 类型一样指定秒的小数部分了。示例如下：


SELECT CAST('2007-10-20 20:30:05.1234567' as DATE)

结果：
2007-10-20

SELECT CAST('2007-10-20 20:30:05.1234567' as TIME)

结果： 20:30:05.1234567

DATETIMEOFFSET

对时区敏感的值现在在 SQL2008 里也可以使用新的数据类型 DATETIMEOFFSET 来处理了。该新类型结合了 DATETIME2 和一个偏移量部分来显示，如下：


SELECT CAST('2007-10-20 20:30:05.1234567 +5:0' as DATETIMEOFFSET)

结果：2007-10-20 20:30:05.1234567 +05:00

和 DATETIME2 一样， DATETIMEOFFSET 也有一个可选的参数（ n ）来指定秒后的小数位数，如下：

SELECT CAST('2007-10-20 20:30:05.1234567 +5:0' as DATETIMEOFFSET(3))

结果：2007-10-20 20:30:05.123 +05:00

小结

SQL2008 包括了一些新的非常便利的数据类型 DATE 和 TIME 。下面是它们之间互相转换的一些例子：


CAST('2007-10-20 20:30:05.1234567 +5:0' as DATE) = 2007-10-20
CAST('2007-10-20 20:30:05.1234567 +5:0' as TIME(7)) = 20:30:05.1234567
CAST('2007-10-20 20:30:05.123' as SMALLDATETIME) = 2007-10-20 20:30:00
CAST('2007-10-20 20:30:05.123' as DATETIME) = 2007-10-20 20:30:05.123
CAST('2007-10-20 20:30:05.1234567 +5:0' as DATETIME2(7)) = 2007-10-20 20:30:05.1234567
CAST('2007-10-20 20:30:05.1234567 +5:0' as DATETIMEOFFSET(7)) = 2007-10-20 20:30:05.1234567 +05:00