【转载】日期和时间数据类型及函数 (Transact-SQL)
2010-03-04 14:01 Virus-BeautyCode 阅读(1881) 评论(0) 编辑 收藏 举报
原文地址：
本主题的以下各节对所有 Transact-SQL 日期和时间数据类型及函数作了概述。有关日期和时间数据类型及函数共有的信息和示例，请参阅使用日期和时间数据。
- 日期和时间数据类型
- 日期和时间函数
- 用来获取系统日期和时间值的函数
- 用来获取日期和时间部分的函数
- 用来获取日期和时间差的函数
- 用来修改日期和时间值的函数
- 用来设置或获取会话格式的函数
- 用来验证日期和时间值的函数
- 日期和时间相关主题
日期和时间数据类型 下表列出了 Transact-SQL 的日期和时间数据类型。
| 数据类型 | 格式 | 范围 | 精确度 | 存储大小（以字节为单位） | 用户定义的秒的小数精度 | 时区偏移量 |
|---|---|---|---|---|---|---|
|
hh:mm:ss[.nnnnnnn] |
00:00:00.0000000 到 23:59:59.9999999 |
100 纳秒 |
3 到 5 |
有 |
无 | |
|
YYYY-MM-DD |
0001-01-01 到 9999-12-31 |
1 天 |
3 |
无 |
无 | |
|
YYYY-MM-DD hh:mm:ss |
1900-01-01 到 2079-06-06 |
1 分钟 |
4 |
无 |
无 | |
|
YYYY-MM-DD hh:mm:ss[.nnn] |
1753-01-01 到 9999-12-31 |
0.00333 秒 |
8 |
无 |
无 | |
|
YYYY-MM-DD hh:mm:ss[.nnnnnnn] |
0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 |
100 纳秒 |
6 到 8 |
有 |
无 | |
|
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 时间表示） |
100 纳秒 |
8 到 10 |
有 |
有 |
.gif) 注意： |
|---|
| Transact-SQL rowversion 数据类型不是日期或时间数据类型。timestamp 是 rowversion 的同义词，但不推荐使用。 |
日期和时间函数 以下各表列出了 Transact-SQL 的日期和时间函数。有关确定性的详细信息，请参阅确定性函数和不确定性函数。
用来获取系统日期和时间值的函数
所有系统日期和时间值均得自运行 SQL Server 实例的计算机的操作系统。
精度较高的系统日期和时间函数
SQL Server 2008 使用 GetSystemTimeAsFileTime() Windows API 来获取日期和时间值。精确程度取决于运行 SQL Server 实例的计算机硬件和 Windows 版本。此 API 的精度固定为 100 纳秒。可通过使用 GetSystemTimeAdjustment() Windows API 来确定该精确度。
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
SYSDATETIME () |
返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 |
datetime2(7) |
不具有确定性 | |
|
SYSDATETIMEOFFSET ( ) |
返回包含计算机的日期和时间的 datetimeoffset(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量包含在内。 |
datetimeoffset(7) |
不具有确定性 | |
|
SYSUTCDATETIME ( ) |
返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。 |
datetime2(7) |
不具有确定性 |
精度较低的系统日期和时间函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
CURRENT_TIMESTAMP |
返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 |
datetime |
不具有确定性 | |
|
GETDATE ( ) |
返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。时区偏移量未包含在内。 |
datetime |
不具有确定性 | |
|
GETUTCDATE ( ) |
返回包含计算机的日期和时间的 datetime2(7) 值，SQL Server 的实例正在该计算机上运行。日期和时间作为 UTC 时间（通用协调时间）返回。 |
datetime |
不具有确定性 |
用来获取日期和时间部分的函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
DATENAME ( datepart , date ) |
返回表示指定日期的指定 datepart 的字符串。 |
nvarchar |
不具有确定性 | |
|
DATEPART ( datepart , date ) |
返回表示指定 date 的指定 datepart 的整数。 |
int |
不具有确定性 | |
|
DAY ( date ) |
返回表示指定 date 的“日”部分的整数。 |
int |
具有确定性 | |
|
MONTH ( date ) |
返回表示指定 date 的“月”部分的整数。 |
int |
具有确定性 | |
|
YEAR ( date ) |
返回表示指定 date 的“年”部分的整数。 |
int |
具有确定性 |
用来获取日期和时间差的函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
DATEDIFF ( datepart , startdate , enddate ) |
返回两个指定日期之间所跨的日期或时间 datepart 边界的数目。 |
int |
具有确定性 |
用来修改日期和时间值的函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
DATEADD (datepart , number , date ) |
通过将一个时间间隔与指定 date 的指定 datepart 相加，返回一个新的 datetime 值。 |
date 参数的数据类型。 |
具有确定性 | |
|
SWITCHOFFSET (DATETIMEOFFSET , time_zone) |
SWITCH OFFSET 更改 DATETIMEOFFSET 值的时区偏移量并保留 UTC 值。 |
具有 DATETIMEOFFSET 的小数精度的 datetimeoffset |
具有确定性 | |
|
TODATETIMEOFFSET (expression , time_zone) |
TODATETIMEOFFSET 将 datetime2 值转换为 datetimeoffset 值。datetime2 值被解释为指定 time_zone 的本地时间。 |
具有 datetime 参数的小数精度的 datetimeoffset |
具有确定性 |
用来设置或获取会话格式的函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
@@DATEFIRST |
返回对会话进行 SET DATEFIRST 操作所得结果的当前值。 |
tinyint |
不具有确定性 | |
|
SET DATEFIRST { number | @number_var } |
将一周的第一天设置为从 1 到 7 的一个数字。 |
不适用 |
不适用 | |
|
SET DATEFORMAT { format | @format_var } |
设置用于输入 datetime 或 smalldatetime 数据的日期各部分（月/日/年）的顺序。 |
不适用 |
不适用 | |
|
@@LANGUAGE |
返回当前使用的语言的名称。@@LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。 |
不适用 |
不适用 | |
|
SET LANGUAGE { [ N ] 'language' | @language_var } |
设置会话和系统消息的语言环境。SET LANGUAGE 不是日期或时间函数。但是，语言设置会影响日期函数的输出。 |
不适用 |
不适用 | |
|
sp_helplanguage [ [ @language = ] 'language' ] |
返回有关所有支持语言日期格式的信息。sp_helplanguage 不是日期或时间存储过程。但是，语言设置会影响日期函数的输出。 |
不适用 |
不适用 |
用来验证日期和时间值的函数
| 函数 | 语法 | 返回值 | 返回数据类型 | 确定性 |
|---|---|---|---|---|
|
ISDATE ( expression ) |
确定 datetime 或 smalldatetime 输入表达式是否为有效的日期或时间值。 |
int |
只有与 CONVERT 函数一起使用，同时指定了 CONVERT 样式参数且样式不等于 0、100、9 或 109 时，ISDATE 才是确定的。 |
sql时间函数详解
select getdate()
2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000
3. datediff 返回跨两个指定日期的日期和时间边界数。
select datediff(day,'2004-09-01','2004-09-18') --返回：17
4. datepart 返回代表指定日期的指定日期部分的整数。
SELECT DATEPART(month, '2004-10-15') --返回 10
5. datename 返回代表指定日期的指定日期部分的字符串
SELECT datename(weekday, '2004-10-15') --返回：星期五
6. day(), month(),year() --可以与datepart对照一下
select 当前日期=convert(varchar(10),getdate(),120)
,当前时间=convert(varchar(8),getdate(),114)
select datename(dw,'2004-10-15')
select 本年第多少周=datename(week,'2004-10-15')
,今天是周几=datename(weekday,'2004-10-15')
函数 参数/功能
GetDate( ) 返回系统目前的日期与时间
DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1
DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期
DatePart (interval,date) 返回日期date中，interval指定部分所对应的整数值
DateName (interval,date) 返回日期date中，interval指定部分所对应的字符串名称
参数 interval的设定值如下：
值 缩 写（Sql Server） Access 和 ASP 说明
Year Yy yyyy 年 1753 ~ 9999
Quarter Qq q 季 1 ~ 4
Month Mm m 月1 ~ 12
Day of year Dy y 一年的日数,一年中的第几日 1-366
Day Dd d 日，1-31
Weekday Dw w 一周的日数，一周中的第几日 1-7
Week Wk ww 周，一年中的第几周 0 ~ 51
Hour Hh h 时0 ~ 23
Minute Mi n 分钟0 ~ 59
Second Ss s 秒 0 ~ 59
Millisecond Ms - 毫秒 0 ~ 999
access 和 asp 中用date()和now()取得系统日期时间；其中DateDiff,DateAdd,DatePart也同是能用于Access和asp中，这些函数的用法也类似
举例：
1.GetDate() 用于sql server :select GetDate()
2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天
3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年
SQL Server DATEPART() 函数返回 SQLServer datetime 字段的一部分。
SQL Server DATEPART() 函数的语法是：
DATEPART(portion, datetime)
其中 datetime 是 SQLServer datetime 字段和部分的名称是下列之一： Ms for Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second
详细的说明:
通常，你需要获得当前日期和计算一些其他的日期，例如，你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割（年、月、日等），然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期！在这篇文章里，我将告诉你如何使用DATEADD和DATEDIFF函数来计算出在你的程序中可能你要用到的一些不同日期。
在使用本文中的例子之前，你必须注意以下的问题。大部分可能不是所有例子在不同的机器上执行的结果可能不一样，这完全由哪一天是一个星期的第一天这个设置决定。第一天（DATEFIRST）设定决定了你的系统使用哪一天作为一周的第一天。所有以下的例子都是以星期天作为一周的第一天来建立，也就是第一天设置为7。假如你的第一天设置不一样，你可能需要调整这些例子，使它和不同的第一天设置相符合。你可以通过@@DATEFIRST函数来检查第一天设置。
为了理解这些例子，我们先复习一下DATEDIFF和DATEADD函数。DATEDIFF函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。DATEADD函数计算一个日期通过给时间间隔加减来获得一个新的日期。要了解更多的DATEDIFF和DATEADD函数以及时间间隔可以阅读微软联机帮助。
使用DATEDIFF和DATEADD函数来计算日期，和本来从当前日期转换到你需要的日期的考虑方法有点不同。你必须从时间间隔这个方面来考虑。比如，从当前日期到你要得到的日期之间有多少时间间隔，或者，从今天到某一天（比如1900-1-1）之间有多少时间间隔，等等。理解怎样着眼于时间间隔有助于你轻松的理解我的不同的日期计算例子。
一个月的第一天
第一个例子，我将告诉你如何从当前日期去这个月的最后一天。请注意：这个例子以及这篇文章中的其他例子都将只使用DATEDIFF和DATEADD函数来计算我们想要的日期。每一个例子都将通过计算但前的时间间隔，然后进行加减来得到想要计算的日期。
这是计算一个月第一天的SQL 脚本：
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
我们把这个语句分开来看看它是如何工作的。最核心的函数是getdate()，大部分人都知道这个是返回当前的日期和时间的函数。下一个执行的函数DATEDIFF(mm,0,getdate())是计算当前日期和“1900-01-01 00:00:00.000”这个日期之间的月数。记住：时期和时间变量和毫秒一样是从“1900-01-01 00:00:00.000”开始计算的。这就是为什么你可以在DATEDIFF函数中指定第一个时间表达式为“0”。下一个函数是DATEADD，增加当前日期到“1900-01-01”的月数。通过增加预定义的日期“1900-01-01”和当前日期的月数，我们可以获得这个月的第一天。另外，计算出来的日期的时间部分将会是“00:00:00.000”。
这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用来计算很多不同的日期。下一个例子也是用这个技巧从当前日期来产生不同的日期。
本周的星期一
这里我是用周(wk)的时间间隔来计算哪一天是本周的星期一。
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
一年的第一天
现在用年(yy)的时间间隔来显示这一年的第一天。
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
季度的第一天
假如你要计算这个季度的第一天，这个例子告诉你该如何做。
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
当天的半夜
曾经需要通过getdate()函数为了返回时间值截掉时间部分，就会考虑到当前日期是不是在半夜。假如这样，这个例子使用DATEDIFF和DATEADD函数来获得半夜的时间点。
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
深入DATEDIFF和DATEADD函数计算
你可以明白，通过使用简单的DATEDIFF和DATEADD函数计算，你可以发现很多不同的可能有意义的日期。
目前为止的所有例子只是仅仅计算当前的时间和“1900-01-01”之间的时间间隔数量，然后把它加到“1900-01-01”的时间间隔上来计算出日期。假定你修改时间间隔的数量，或者使用不同的时间间隔来调用DATEADD函数，或者减去时间间隔而不是增加，那么通过这些小的调整你可以发现和多不同的日期。
这里有四个例子使用另外一个DATEADD函数来计算最后一天来分别替换DATEADD函数前后两个时间间隔。
上个月的最后一天
这是一个计算上个月最后一天的例子。它通过从一个月的最后一天这个例子上减去3毫秒来获得。有一点要记住，在Sql Server中时间是精确到3毫秒。这就是为什么我需要减去3毫秒来获得我要的日期和时间。
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
计算出来的日期的时间部分包含了一个Sql Server可以记录的一天的最后时刻(“23:59:59:997”)的时间。
去年的最后一天
连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去3毫秒。
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
本月的最后一天
现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。修改需要给用DATEDIFF比较当前日期和“1900-01-01”返回的时间间隔上加1。通过加1个月，我计算出下个月的第一天，然后减去3毫秒，这样就计算出了这个月的最后一天。这是计算本月最后一天的SQL脚本。
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
本年的最后一天
你现在应该掌握这个的做法，这是计算本年最后一天脚本
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
本月的第一个星期一
好了，现在是最后一个例子。这里我要计算这个月的第一个星期一。这是计算的脚本。
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
在这个例子里，我使用了“本周的星期一”的
