SQL查询时间差：多种单位示例及应用

 
<P>"select count(*) from [注册] where datediff(day,time,getdate())&lt;1";//获取当天注册人员数<BR>sql="select id,classid,classname,xiaoshoue,xiaoshou,jinhuo,kucun,addtime " <BR>sql=sql&amp;" from xiaoshou where addtime between '" &amp; format(starttime,"yyyy-mm-dd") &amp; "' and '" &amp; format(endtime,"yyyy-mm-dd") &amp; "'"<BR>sql=sql&amp;" order by id desc" </P>
<P>Select Fylb,Pm,Gg,Dw,Dj,Sl,Je FROM cqyzypzx where zxdate between {^2003-7-1}and {^2003-8-1}</P>
<P>近两个星期 <BR>sql="select * from table where datediff(week,riqi,getdate())&lt;=2" </P>
<P>近三个月 <BR>sql="select * from table where datediff(month,riqi,getdate())&lt;=3" </P>
<P>近一年 <BR>sql="select * from table where datediff(year,riqi,getdate())&lt;=1" </P>
<P>Apply_Date between #"&amp; startdate &amp;" 23:59:59# and #"&amp; enddate &amp;" 23:59:59#"&nbsp; </P>
<P>&nbsp;</P>
<P>参考以下日期写法<BR>---求相差天数<BR>select datediff(day,'2004-01-01',getdate())</P><PRE><SPAN style="COLOR: #ff0000">SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate</SPAN></PRE>
<P><SPAN style="COLOR: #ff0000">结果：</SPAN></P>
<P>
<TABLE style="WIDTH: 30%" class=dataintable>
<TBODY>
<TR>
<TH><SPAN style="COLOR: #ff0000">DiffDate</SPAN></TH></TR>
<TR>
<TD><SPAN style="COLOR: #ff0000">1</SPAN></TD></TR></TBODY></TABLE><BR>转贴:<BR>--1.一个月第一天的<BR>SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)</P>
<P>--2.本周的星期一<BR>SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)</P>
<P>select dateadd(wk,datediff(wk,0,getdate()),6)<BR>--3.一年的第一天<BR>SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)</P>
<P>--4.季度的第一天<BR>SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)</P>
<P>--5.当天的半夜<BR>SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)</P>
<P>--6.上个月的最后一天<BR>SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))</P>
<P>--7.去年的最后一天<BR>SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))</P>
<P>--8.本月的最后一天<BR>SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))</P>
<P>--9.本年的最后一天<BR>SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))</P>
<P>--10.本月的第一个星期一<BR>select DATEADD(wk,<BR>DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)</P>
<P><BR>&nbsp;</P>
<P>--查询本周注册人数<BR>select count(*) from [user]<BR>where datediff(week,create_day-1,getdate())=0</P>
<P>--上周注册人数<BR>select count(*) from [user]<BR>where datediff(week,create_day-1,getdate())=1</P>
<P><BR>--本月注册人数<BR>select count(*) from [user]<BR>where datediff(month,create_day,getdate())=0</P>