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
--王成辉翻译整理,转贴请注明出自微软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