SQL日期和时间函数

函数 参数/功能 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的设定值如下：

1.一个月第一天的 Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

2.本周的星期一 Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

3.一年的第一天 Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

4.季度的第一天 Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

5.当天的半夜 Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

6.上个月的最后一天 Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

7.去年的最后一天 Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

8.本月的最后一天 Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

9.本年的最后一天 Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

10. 本月的第一个星期一 select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0) 返回当前日期和时间通过函数 GETDATE （），你可以获得当前的日期和时间。函数 GETDATE （）可以用来作为 DATEDIME 型字段的缺省值。这对插入记录时保存当时的时间是有用的。要建立一个表，其中的记录包含有当前的日期和时间，可以添加一个 DATETIME 型字段，指定其缺省值为函数 GETDATE （）的返回值，就象这样： Create TABLE site_log ( username VARCHAR(40) ， useractivity VARCHAR(100) ， entrydate DATETIME DEFAULT GETDATE()) 转换日期和时间函数 GETDATE （）的返回值在显示时只显示到秒。实际上， SQL Sever 内部时间可以精确到毫秒级（确切地说，可以精确到 3.33 毫秒）。要得到不同格式的日期和时间，你需要使用函数 CONVERT （）。例如，当下面的这个语句执行时，显示的时间将包括毫秒： Select CONVERT(VARCHAR(30) ， GETDATE() ， 9) 注意例子中数字 9 的使用。这个数字指明了在显示日期和时间时使用哪种日期和时间格式。当这个语句执行时，将显示如下的日期和时间： Nov 30 1997 3:29:55:170AM (1 row(s) affected) 在函数 CONVERT （）中你可以使用许多种不同风格的日期和时间格式。下表显示了所有的格式。 日期和时间的类型：类型值 标准 输出 0 Default mon dd yyyy hh:miAM 1 USA mm/dd/yy 2 ANSI yy.mm.dd 3 British/French dd/mm/yy 4 German dd.mm.yy 5 Italian dd-mm-yy 6 - dd mon yy 7 - mon dd ， yy 8 - hh:mi:ss 9 Default + milliseconds--mon dd yyyy hh:mi:ss:mmmAM(or ) 10 USA mm-dd-yy 11 JAPAN yy/mm/dd 12 ISO yymmdd 13 Europe Default + milliseconds--dd mon yyyy 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 年）。例如，要按日本标准显示日期，包括世纪，你应使用如下的语句： Select CONVERT （ VARCHAR （ 30 ）， GETDATE （）， 111 ） 在这个例子中，函数 CONVERT （）把日期格式进行转换，显示为 1997/11/30 抽取日期和时间在许多情况下，你也许只想得到日期和时间的一部分，而不是完整的日期和时间。为了抽取日期的特定部分，你可以使用函数 DATEPART （），象这样： Select site_name ‘ Site Name ’， DATEPART(mm ， site_entrydate) ‘ Month Posted ’ FROM site_directory 函数 DATEPART （）的参数是两个变量。第一个变量指定要抽取日期的哪一部分；第二个变量是实际的数据。在这个例子中，函数 DATEPART （）抽取月份，因为 mm 代表月份。下面是这个 Select 语句的输出结果： Site Name Month Posted ……………………………………………………………… Yahoo 2 Microsoft 5 Magicw3 5 (3 row(s) affected) Month Posted 列显示了每个站点被查询的月份。函数 DATEPART （）的返回值是一个整数。你可以用这个函数抽取日期的各个不同部分，如下表所示。 日期的各部分及其简写日期部分 简写 值 year yy 1753--9999 quarter qq 1--4 month mm 1--12 day of year dy 1--366 day dd 1--31 week wk 1--53 weekday dw 1--7(Sunday--Saturday) hour hh 0--23 minute mi 0--59 second ss 0--59 milisecond ms 0--999 当你需要进行日期和时间的比较时，使用函数 DATEPART （）返回整数是有用的。但是，上例中的查询结果（ 2 ， 5 ）不是十分易读。要以更易读的格式得到部分的日期和时间，你可以使用函数 DATENAME （），如下例所示： Select site_name ‘ Site Name ’ DATENAME(mm ， site_entrydate) ‘ Month Posted ’ FROM site_directory 函数 DATENAME （）和函数 DATEPART （）接收同样的参数。但是，它的返回值是一个字符串，而不是一个整数。下面是上例该用 DATENAME （）得到的结果： Site Name Month Postec …………………………………………………………………. Yahoo February Microsoft June Magicw3 June (3 row(s) affected) 你也可以用函数 DATENAE （）来抽取一个星期中的某一天。下面的这个例子同时抽取一周中的某一天和日期中的月份： Select site_name ‘ Site Name ’， DATENAME(dw ， site_entrydate)+ ‘ - ’ + DATENAME （ mm ， site_entrydate ） ‘ Day and Month Posted ’ FORM site_directory 这个例子执行时，将返回如下的结果： Site Name Day and Month Posted ……………………………………………………………………… Yahoo Friday - February Microsoft Tuesday - June Magicw3 Monday - June (3 row(s) affected) 返回日期和时间范围当你分析表中的数据时，你也许希望取出某个特定时间的数据。你也许对特定的某一天中 ?D?D 比如说 2000 年 12 月 25 日 ?D?D 访问者在你站点上的活动感兴趣。要取出这种类型的数据，你也许会试图使用这样的 Select 语句： Select * FROM weblog Where entrydate="12/25/20000" 不要这样做。这个 Select 语句不会返回正确的记录 ?D?D 它将只返回日期和时间是 12/25/2000 12:00:00:000AM 的记录。换句话说，只有刚好在午夜零点输入的记录才被返回。 问题是 SQL Sever 将用完整的日期和时间代替部分日期和时间。例如，当你输入一个日期，但不输入时间时， SQL Sever 将加上缺省的时间“ 12 ： 00 ： 00 ： 000AM ”。当你输入一个时间，但不输入日期时， SQL Sever 将加上缺省的日期“ Jan 1 1900 ”。要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。例如，下面的这个 Select 语句将能返回正确的记录： Select * FROM weblog Where entrydate>=”12/25/2000” AND entrydate<”12/26/2000” 这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于 12/25/2000 12:00:00:000AM 并小于 12/26/2000 12:00:00:000AM 的记录。换句话说，它将正确地返回 2000 年圣诞节这一天输入的每一条记录。另一种方法是，你可以使用 LIKE 来返回正确的记录。通过在日期表达式中包含通配符“％”，你可以匹配一个特定日期的所有时间。这里有一个例子： Select * FROM weblog Where entrydate LIKE ‘Dec 25 2000%’ 这个语句可以匹配正确的记录。因为通配符“％”代表了任何时间。使用这两种匹配日期和时间范围的函数，你可以选择某个月，某一天，某一年，某个小时，某一分钟，某一秒，甚至某一毫秒内输入的记录。但是，如果你使用 LIKE 来匹配秒或毫秒，你首先需要使用函数 CONVERT （）把日期和时间转换为更精确的格式（参见前面“转换日期和时间”一节）。 比较日期和时间最后，还有两个日期和时间函数对根据日期和时间取出记录是有用的。使用函数 DATEADD （）和 DATEDIFF （），你可以比较日期的早晚。例如，下面的 Select 语句将显示表中的每一条记录已经输入了多少个小时： Select entrydate ‘ Time Entered ’ DATEDIFF(hh ， entrydate ， GETDATE()) ‘ Hours Ago ’ FROM weblog 如果当前时间是 2000 年 11 月 30 号下午 6 点 15 分，则会返回如下的结果： Time Entered Hours Ago ………………………………………………….. Dec 30 2000 4:09PM 2 Dec 30 2000 4:13PM 2 Dec 1 2000 4:09PM 698 (3 row(s) affected) 函数 DADEDIFF （）的参数是三个变量。第个变量指定日期的某一部分。在这个例子中，是按小时对日期进行比较，（要了解日期各部分的详细内容，请参考表 11.2 ）在日期 2000 年 11 月 1 日和 2000 年 11 月 30 日的指定时间之间有 689 个小时。另外两个参数是要进行比较的时间。为了返回一个正数，较早的时间应该先给。函数 DATEADD （）把两个日期相加。当你需要计算截止日期这一类的数据时，这个函数是有用处的。假如你要查询一个月前注册用户的记录，你可以使用如下的 Select 语句： Select username ‘ User Name ’， DATEADD(mm ， 1 ， firstvisit_date) ‘ Registration Expires’ FROM registration_table 函数 DATEADD （）的参数有三个变量。第一个变量代表日期的某一部分，这个例子用到了代表月份的 mm 。第二个变量指定了时间的间隔 ?D?D 在本例中是一个月。最后一个变量是一个日期，在这个例子中，日期是取自 DATETIME 型字段 firstvisit_date. 假设当前日期是 June 30 ， 2000 ，这个语句将返回如下的内容： User Name Registration Expires …………………………………………………………………………… Bill Gates Jul 30 2000 4:09PM President Clinton Jul 30 2000 4:13PM William Shakespeare Jul 1 2000 4:09PM (3 row(s) affected) 注意：使用函数 DATEADD （）把一个日期加上一个月，它并不加上 30 天。这个函数只简单地把月份值加 1