SQL时间函数.docx

SQL时间函数.docx

《SQL时间函数.docx》由会员分享，可在线阅读，更多相关《SQL时间函数.docx（19页珍藏版）》请在冰点文库上搜索。

SQL时间函数

SQL时间函数

2007-12-2016:

00

中日期函数的用法

悬赏分：

5-解决时间：

2007-3-1320:

40

①DATEADD\DAY\DATEDIFF\GETDATE\DATENAME等函数的用法.

②想查询2007年2月9日的付款流水有单笔交易出现多张小票号的情况的小票号,该用什么语句?

提问者：

420329630-助理二级

最佳答案

1.DateAdd（I,N,D）

将一个日期加上一段期间后的日期。

I：

设定一个日期（Date）所加上的一段期间的单位。

譬如interval="d"表示N的单位为日。

I的设定值如下：

yyyyYear年

qQuarter季

mMonth月

dDay日

wWeekday星期

hHour时

nMinute分

sSecond秒

N：

数值表达式，设定一个日期所加上的一段期间，可为正值或负值，正值表示加（结果为>date以后的日期），负值表示减（结果为>date以前的日期）。

D：

待加减的日期。

例子：

DateAdd（"m",1,"31-Jan-98"）

结果：

28-Feb-98

说明：

将日期31-Jan-98加上一个月，结果为28-Feb-98而非31-Fe-98。

例子：

DateAdd（"d",20,"30-Jan-99"）

结果：

1999/2/9

说明：

将一个日期30-Jan-99加上20天后的日期。

2.Day（日期的字符串或表达式）

传回日期的「日」部份。

例子：

Day（"12/1/1999"）

结果：

1

3.DateDiff（I,D1,D2[,FW[,FY]]）

计算两个日期之间的期间。

I：

设定两个日期之间的期间计算之单位。

譬如>I="m"表示计算的单位为月。

>I的设定值如：

yyyy>Year年

qQuarter季

mMonth月

dDay日

wWeekday星期

hHour时

nMinute分

sSecond秒

D1,D2：

计算期间的两个日期表达式，若>date1较早，则两个日期之间的期间结果为正值；若>date2较早，则结果为负值。

FW：

设定每周第一天为星期几，若未设定表示为星期天。

>FW的设定值如下：

0使用>API的设定值。

1星期天

2星期一

3星期二

4星期三

5星期四

6星期五

7星期六

FY：

设定一年的第一周，若未设定则表示一月一日那一周为一年的第一周。

>FY的设定值如下：

0使用>API的设定值。

1一月一日那一周为一年的第一周

2至少包括四天的第一周为一年的第一周

3包括七天的第一周为一年的第一周

例子：

DateDiff（"d","25-Mar-99","30-Jun-99"）

结果：

97

说明：

显示两个日期之间的期间为97天

想查询2007年2月9日的付款流水有单笔交易出现多张小票号的情况的小票号

这个要看一下你的表结构是什么样的啊？

回答者：

qqsmalltiger-助理二级3-323:

45

评价已经被关闭   目前有0个人评价

      好

50%（0）不好

50%（0）

其他回答   共1条

DATEADD

在向指定日期加上一段时间的基础上，返回新的datetime值。

语法

DATEADD（datepart,number,date）

DATEDIFF

返回跨两个指定日期的日期和时间边界数。

语法

DATEDIFF（datepart,startdate,enddate）

GETDATE

按datetime值的Microsoft®SQLServer™标准内部格式返回当前系统日期和时间。

语法

GETDATE（）

DATENAME

返回代表指定日期的指定日期部分的字符串。

语法

DATENAME（datepart,date）

SQL日期计算

通常，你需要获得当前日期和计算一些其他的日期，例如，你的程序可能需要判断一个月的第一天或者最后一天

。

你们大部分人大概都知道怎样把日期进行分割（年、月、日等），然后仅仅用分割出来的年、月、日等放在几

个函数中计算出自己所需要的日期！

在这篇文章里，我将告诉你如何使用DATEADD和DATEDIFF函数来计算出在你

的程序中可能你要用到的一些不同日期。

      在使用本文中的例子之前，你必须注意以下的问题。

大部分可能不是所有例子在不同的机器上执行的结

果可能不一样，这完全由哪一天是一个星期的第一天这个设置决定。

第一天（DATEFIRST）设定决定了你的系统

使用哪一天作为一周的第一天。

所有以下的例子都是以星期天作为一周的第一天来建立，也就是第一天设置为7

。

假如你的第一天设置不一样，你可能需要调整这些例子，使它和不同的第一天设置相符合。

你可以通过

@@DATEFIRST函数来检查第一天设置。

      为了理解这些例子，我们先复习一下DATEDIFF和DATEADD函数。

DATEDIFF函数计算两个日期之间的小时、

天、周、月、年等时间间隔总数。

DATEADD函数计算一个日期通过给时间间隔加减来获得一个新的日期。

要了解

更多的DATEDIFF和DATEADD函数以及时间间隔可以阅读微软联机帮助。

      使用DATEDIFF和DATEADD函数来计算日期，和本来从当前日期转换到你需要的日期的考虑方法有点不同。

你必须从时间间隔这个方面来考虑。

比如，从当前日期到你要得到的日期之间有多少时间间隔，或者，从今天到

某一天（比如1900-1-1）之间有多少时间间隔，等等。

理解怎样着眼于时间间隔有助于你轻松的理解我的不同的

日期计算例子。

一个月的第一天

      第一个例子，我将告诉你如何从当前日期去这个月的最后一天。

请注意：

这个例子以及这篇文章中的其

他例子都将只使用DATEDIFF和DATEADD函数来计算我们想要的日期。

每一个例子都将通过计算但前的时间间隔，

然后进行加减来得到想要计算的日期。

      这是计算一个月第一天的SQL脚本：

      SELECTDATEADD（mm,DATEDIFF（mm,0,getdate（））,0）

      我们把这个语句分开来看看它是如何工作的。

最核心的函数是getdate（），大部分人都知道这个是返回当

前的日期和时间的函数。

下一个执行的函数DATEDIFF（mm,0,getdate（））是计算当前日期和“1900-01-01

00:

00:

00.000”这个日期之间的月数。

记住：

时期和时间变量和毫秒一样是从“1900-01-0100:

00:

00.000”开

始计算的。

这就是为什么你可以在DATEDIFF函数中指定第一个时间表达式为“0”。

下一个函数是DATEADD，增加

当前日期到“1900-01-01”的月数。

通过增加预定义的日期“1900-01-01”和当前日期的月数，我们可以获得这

个月的第一天。

另外，计算出来的日期的时间部分将会是“00:

00:

00.000”。

      这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来

获得特殊的日期，这个技巧可以用来计算很多不同的日期。

下一个例子也是用这个技巧从当前日期来产生不同的

日期。

本周的星期一

      这里我是用周（wk）的时间间隔来计算哪一天是本周的星期一。

      SELECTDATEADD（wk,DATEDIFF（wk,0,getdate（））,0）

一年的第一天

      现在用年（yy）的时间间隔来显示这一年的第一天。

      SELECTDATEADD（yy,DATEDIFF（yy,0,getdate（））,0）

季度的第一天

      假如你要计算这个季度的第一天，这个例子告诉你该如何做。

      SELECTDATEADD（qq,DATEDIFF（qq,0,getdate（））,0）

当天的半夜

      曾经需要通过getdate（）函数为了返回时间值截掉时间部分，就会考虑到当前日期是不是在半夜。

假如这

样，这个例子使用DATEDIFF和DATEADD函数来获得半夜的时间点。

      SELECTDATEADD（dd,DATEDIFF（dd,0,getdate（））,0）

深入DATEDIFF和DATEADD函数计算

      你可以明白，通过使用简单的DATEDIFF和DATEADD函数计算，你可以发现很多不同的可能有意义的日期。

      目前为止的所有例子只是仅仅计算当前的时间和“1900-01-01”之间的时间间隔数量，然后把它加到

“1900-01-01”的时间间隔上来计算出日期。

假定你修改时间间隔的数量，或者使用不同的时间间隔来调用

DATEADD函数，或者减去时间间隔而不是增加，那么通过这些小的调整你可以发现和多不同的日期。

      这里有四个例子使用另外一个DATEADD函数来计算最后一天来分别替换DATEADD函数前后两个时间间隔。

上个月的最后一天

      这是一个计算上个月最后一天的例子。

它通过从一个月的最后一天这个例子上减去3毫秒来获得。

有一点

要记住，在SqlServer中时间是精确到3毫秒。

这就是为什么我需要减去3毫秒来获得我要的日期和时间。

      SELECTdateadd（ms,-3,DATEADD（mm,DATEDIFF（mm,0,getdate（））,0））

     计算出来的日期的时间部分包含了一个SqlServer可以记录的一天的最后时刻（“23:

59:

59:

997”）的时

间。

去年的最后一天

      连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去3毫秒。

      SELECTdateadd（ms,-3,DATEADD（yy,DATEDIFF（yy,0,getdate（））,0））

本月的最后一天

      现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。

修改需要给用

DATEDIFF比较当前日期和“1900-01-01”返回的时间间隔上加1。

通过加1个月，我计算出下个月的第一天，然后

减去3毫秒，这样就计算出了这个月的最后一天。

这是计算本月最后一天的SQL脚本。

      SELECTdateadd（ms,-3,DATEADD（mm,DATEDIFF（m,0,getdate（））+1,0））

本年的最后一天

      你现在应该掌握这个的做法，这是计算本年最后一天脚本

      SELECTdateadd（ms,-3,DATEADD（yy,DATEDIFF（yy,0,getdate（））+1,0））。

本月的第一个星期一

      好了，现在是最后一个例子。

这里我要计算这个月的第一个星期一。

这是计算的脚本。

        selectDATEADD（wk,DATEDIFF（wk,0,                                                     

                              dateadd（dd,6-datepart（day,getdate（））,getdate（）））,0）             

      在这个例子里，我使用了“本周的星期一”的脚本，并作了一点点修改。

修改的部分是把原来脚本中

“getdate（）”部分替换成计算本月的第6天，在计算中用本月的第6天来替换当前日期使得计算可以获得这个月

的第一个星期一。

总结

      我希望这些例子可以在你用DATEADD和DATEDIFF函数计算日期时给你一点启发。

通过使用这个计算日期的

时间间隔的数学方法，我发现为了显示两个日期之间间隔的有用历法是有价值的。

注意，这只是计算出这些日期

的一种方法。

要牢记，还有很多方法可以得到相同的计算结果。

假如你有其他的方法，那很不错，要是你没有，

我希望这些例子可以给你一些启发，当你要用DATEADD和DATEDIFF函数计算你程序可能要用到的日期时。

---------------------------------------------------------------

附录，其他日期处理方法

1）去掉时分秒

declare@datetime

set@=getdate（）--'2003-7-110:

00:

00'

SELECT@,DATEADD（day,DATEDIFF（day,0,@）,0）

2）显示星期几

selectdatename（weekday,getdate（））  

3）如何取得某个月的天数

declare@mint

set@m=2--月份

select   datediff（day,'2003-'+cast（@masvarchar）+'-15','2003-'+cast（@m+1   asvarchar）+'-

15'）

另外，取得本月天数

select   datediff（day,cast（month（GetDate（））asvarchar）+'-'+cast（month（GetDate（））as

varchar）+'-15',cast（month（GetDate（））asvarchar）+'-'+cast（month（GetDate（））+1   asvarchar）

+'-15'）

或者使用计算本月的最后一天的脚本，然后用DAY函数区最后一天

SELECTDay（dateadd（ms,-3,DATEADD（mm,DATEDIFF（m,0,getdate（））+1,0）））

4）判断是否闰年：

SELECTcaseday（dateadd（mm,2,dateadd（ms,-3,DATEADD（yy,DATEDIFF（yy,0,getdate（））,0））））

when28then'平年'else'闰年'end

或者

selectcasedatediff（day,datename（year,getdate（））+'-02-01',dateadd（mm,1,datename（year,getdate

（））+'-02-01'））

when28then'平年'else'闰年'end

5）一个季度多少天

declare@mtinyint,@timesmalldatetime

select@m=month（getdate（））

select@m=casewhen@mbetween1and3then1

                      when@mbetween4and6then4

                      when@mbetween7and9then7

sql日期操作

dumptransaction数据库withno_log

selectdatename（weekday,getdate（））   获取当天的星期数

selectconvert（char（10）,getdate（）,21）

cast（@Today+''+@DutyTime_1asdatetime）字符串组合产生时间类型

ifexists（select1frommaster..sysdatabaseswherename='example'）判断是否有记录存在

cast（ltrim（str（@thisyear,4））+'-'+ltrim（str（@thismonth,2））+'-'+'2100:

00:

00'asdatetime）

1.一个月第一天的

SelectDATEADD（mm,DATEDIFF（mm,0,getdate（））,0）

2.本周的星期一

SelectDATEADD（wk,DATEDIFF（wk,0,getdate（））,0）

3.一年的第一天

SelectDATEADD（yy,DATEDIFF（yy,0,getdate（））,0）

4.季度的第一天

SelectDATEADD（qq,DATEDIFF（qq,0,getdate（））,0）

5.当天的半夜

SelectDATEADD（dd,DATEDIFF（dd,0,getdate（））,0）

6.上个月的最后一天

Selectdateadd（ms,-3,DATEADD（mm,DATEDIFF（mm,0,getdate（））,0））

7.去年的最后一天

Selectdateadd（ms,-3,DATEADD（yy,DATEDIFF（yy,0,getdate（））,0））

8.本月的最后一天

Selectdateadd（ms,-3,DATEADD（mm,DATEDIFF（m,0,getdate（））+1,0））

9.本年的最后一天

Selectdateadd（ms,-3,DATEADD（yy,DATEDIFF（yy,0,getdate（））+1,0））

10.本月的第一个星期一

selectDATEADD（wk,DATEDIFF（wk,0,dateadd（dd,6-datepart（day,getdate（））,getdate（）））,0）

返回当前日期和时间

通过函数GETDATE（），你可以获得当前的日期和时间。

函数GETDATE（）可以用来作为DATEDIME型字段的缺省值。

这对插入记录时保存当时的时间是有用的。

要建立一个表，其中的记录包含有当前的日期和时间，可以添加一个DATETIME型字段，指定其缺省值为函数GETDATE（）的返回值，就象这样：

CreateTABLEsite_log（

usernameVARCHAR（40），

useractivityVARCHAR（100），

entrydateDATETIMEDEFAULTGETDATE（））

转换日期和时间

函数GETDATE（）的返回值在显示时只显示到秒。

实际上，SQLSever内部时间可以精确到毫秒级（确切地说，可以精确到3.33毫秒）。

要得到不同格式的日期和时间，你需要使用函数CONVERT（）。

例如，当下面的这个语句执行时，显示的时间将包括毫秒：

SelectCONVERT（VARCHAR（30），GETDATE（），9）

注意例子中数字9的使用。

这个数字指明了在显示日期和时间时使用哪种日期和时间格式。

当这个语句执行时，将显示如下的日期和时间：

Nov3019973:

29:

55:

170AM

（1row（s）affected）

在函数CONVERT（）中你可以使用许多种不同风格的日期和时间格式。

下表显示了所有的格式。

日期和时间的类型：

类型值标准输出

0Defaultmonddyyyyhh:

miAM

1USAmm/dd/yy

2ANSIyy.mm.dd

3British/Frenchdd/mm/yy

4Germandd.mm.yy

5Italiandd-mm-yy

6-ddmonyy

7-mondd，yy

8-hh:

mi:

ss

9Default+milliseconds--monddyyyy

hh:

mi:

ss:

mmmAM（or）

10USAmm-dd-yy

11JAPANyy/mm/dd

12ISOyymmdd

13EuropeDefault+milliseconds--ddmonyyyy

hh:

mi:

ss:

mmm（24h）

14-hh:

mi:

ss:

mmm（24h）

类型0，9，和13总是返回四位的年。

对其它类型，要显示世纪，把style值加上100。

类型13和14返回24小时时钟的时间。

类型0，7，和13返回的月份用三位字符表示（用Nov代表November）.

对表中所列的每一种格式，你可以把类型值加上100来显示有世纪的年（例如，00年将显示为2000年）。

例如，要按日本标准显示日期，包括世纪，你应使用如下的语句：

SelectCONVERT（VARCHAR（30），GETDATE（），111）

在这个例子中，函数CONVERT（）把日期格式进行转换，显示为1997/11/30

抽取日期和时间

在许多情况下，你也许只想得到日期和时间的一部分，而不是完整的日期和时间。

为了抽取日期的特定部分，你可以使用函数DATEPART（），象这样：

Selectsite_name‘SiteName’，

DATEPART（mm，site_entrydate）‘MonthPosted’FROMsite_directory

函数DATEPART（）的参数是两个变量。

第一个变量指定要抽取日期的哪一部分；第二个变量是实际的数据。

在这个例子中，函数DATEPART（）抽取月份，因为mm代表月份。

下面是这个Select语句的输出结果：

SiteNameMonthPosted

………………………………………………………………

Yahoo2

Microsoft5

Magicw35

（3row（s）affected）

MonthPosted列显示了每个站点被查询的月份。

函数DATEPART（）的返回值是一个整数。

你可以用这个函数抽取日期的各个不同部分，如下表所示。

日期的各部分及其简写

日期部分简写值

yearyy1753--9999

quarterqq1--4

monthmm1--12

dayofyeardy1--366

daydd1--31

weekwk1--53

weekdaydw1--7（Sunday--Saturday）

hourhh0--23

minutemi0--59

secondss0--59

milisecondms0--999

当你需要进行日期和时间的比较时，使用函数DATEPART（）返回整数是有用的。

但是，上例中的查询结果（2，5）不是十分易读。

要以更易读的格式得到部分的日期和时间，你可以使用函数DATENAME（），如下例所示：

Selectsite_name‘SiteName’

DATENAME（mm，site_entrydate）‘MonthPosted’

FROMsite_directory

函数DATENAME（）和函数DATEPART（）接收同样的参数。

但是，它的返回