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