SQL查询月、天、周、年（MySql的实例对比）

SQL Server实现

日期部分	缩写
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

 1
 2 /**/ /*计算今天是星期几*/
 3 select   datename (weekday, getdate ()) 
 4
 5 /**/ /*查询本年的数据*/
 6 select   *   from   users  where   year (time) = year ( getdate ())   
 7
 8 /**/ /*查询本月的数据，time是表users中代表时间的字段*/
 9 select   *   from  users  where   month (time) = month ( getdate ())  and   year (time) = year ( getdate ())
10
11 /**/ /*查询今天的数据，time 是表中代表时间的字段*/
12 select   *   from  users  where   day (time) = day ( getdate ())  and   month (time) = month ( getdate ())  and   year (time) = year ( getdate ())
13
14
15 /**/ /*计算那一天是星期一*/
16 SELECT    DATEADD (wk,   DATEDIFF (wk, 0 , getdate ()),   0 )  
17
18 /**/ /*计算那一天是周末*/
19 select   dateadd (wk, datediff (wk, 0 , getdate ()), 6 )
20
21 /**/ /*查询本周的数据*/
22 select   *   from  users  where   DATEPART (wk, time)  =   DATEPART (wk,  GETDATE ())  and   DATEPART (yy, time)  =   DATEPART (yy,  GETDATE ()) 
23
24
25 /**/ /*查询本日的记录*/
26 select   *   from  users  where  ( DATEDIFF (dd, time,  GETDATE ())  =   0 )
27
28 /**/ /*查询本月的记录*/
29 select   *   from  users  where  ( DATEDIFF (mm, time,  GETDATE ())  =   0 )
30
31 /**/ /*查询本年的记录*/
32 select   *   from  users  where  ( DATEDIFF (yy, time,  GETDATE ())  =   0 )

在MySql中实现：

 1    1 ——   
 2   本年：   
 3    select     *     from    loanInfo    where     year (date) = year ( getdate ())   
 4     
 5    2 ——   
 6   本月：   
 7    select     *     from    loanInfo    where     year (date) = year ( getDate ())    And     month (date) = month ( getdate ())   
 8     
 9    3 ——   
10   本日：   
11    select     *     from    loanInfo    where     year (date) = year ( getDate ())    And     month (date) = month ( getdate ())    and     Day (date) = Day ( getDate ())  
12
13
14
15 SELECT     *      FROM     table      WHERE    ( MONTH (字段)    =     MONTH ( GETDATE ()))