怎么用SQL查询昨天、今天、明天和本周的记录？又怎么用SQL查询一天，三天，一周，一个月，更长一些——一个季度的记录呢？本文中给出了一些方法。
SQL查询今天的记录：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span>datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[Datetime],getdate())=0 把Datetime换为你的相应字段； </span></span></li></ol> |
SQL查询昨天的记录：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span>datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[Datetime],getdate())=1 把Datetime换为你的相应字段，getdate()-Datetime即为时间差。 </span></span></li></ol> |
本月记录：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> 表 </span><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> datediff(</span><span class="func"><font color="#ff1493">month</font></span><span>,[dateadd],getdate())=0 </span></span></li></ol> |
本周记录：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> 表 </span><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> datediff(week,[dateadd],getdate())=0 </span></span></li></ol> |
本日记录：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">SELECT</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">FROM</font></strong></span><span> 表 </span><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[dateadd],getdate())=0 </span></span></li></ol> |
一天
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> T_news </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,addtime,getdate())=0 </span></span></li></ol> |
三天
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> T_news </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,addtime,getdate())<= 2 </span><span class="op"><font color="#808080">and</font></span><span> datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,addtime,getdate())>= 0 </span></span></li></ol> |
一周
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> T_news </span><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) </span><span class="op"><font color="#808080">AND</font></span><span> (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) </span></span></li></ol> |
注意：此时不能用 datediff 差值为7,因为，datediff只表示间隔数
一月
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> T_news </span><span class="keyword"><strong><font color="#006699">WHERE</font></strong></span><span> (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) </span><span class="op"><font color="#808080">AND</font></span><span> (DATEPART(mm, addtime) = DATEPART(mm, GETDATE())) </span></span></li></ol> |
一季度
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> T_news </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) </span><span class="op"><font color="#808080">and</font></span><span> DATEPART(yy, addtime) = DATEPART(yy, GETDATE()) </span></span></li></ol> |
希望以上这些方法，能给大家一些启示。