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

/**//*计算今天是星期几*/ 3

select datename(weekday,getdate()) 4

/**//*查询本年的数据*/ 6

select * from users where year(time)=year(getdate()) 7

/**//*查询本月的数据，time是表users中代表时间的字段*/ 9

select * from users where month(time)=month(getdate()) and year(time)=year(getdate()) 10

/**//*查询今天的数据，time 是表中代表时间的字段*/ 12

select * from users where day(time)=day(getdate()) and month(time)=month(getdate()) and year(time)=year(getdate()) 13

/**//*计算那一天是星期一*/ 16

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) 17

/**//*计算那一天是周末*/ 19

select dateadd(wk,datediff(wk,0,getdate()),6) 20

/**//*查询本周的数据*/ 22

select * from users where DATEPART(wk, time) = DATEPART(wk, GETDATE()) and DATEPART(yy, time) = DATEPART(yy, GETDATE()) 23

/**//*查询本日的记录*/ 26

select * from users where (DATEDIFF(dd, time, GETDATE()) = 0) 27

/**//*查询本月的记录*/ 29

select * from users where (DATEDIFF(mm, time, GETDATE()) = 0) 30

/**//*查询本年的记录*/ 32

select * from users where (DATEDIFF(yy, time, GETDATE()) = 0)

在MySql中实现：

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
15SELECT   *    FROM   table    WHERE   (MONTH(字段)   =   MONTH(GETDATE()))

巴特西

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

最新文章

热门文章