欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Sql Server中查询当天,最近三天,本周,本月,最近一个月,本季度的数据的sql语句

程序员文章站 2024-02-01 19:53:58
--当天: select * from T_news where datediff(day,addtime,getdate())=0 --最近三天: select * from T_news where datediff(day,addtime,getdate())= 0 --本周: select ... ......
--当天:

select * from t_news where datediff(day,addtime,getdate())=0

--最近三天:

select * from t_news where datediff(day,addtime,getdate())<= 2 and datediff(day,addtime,getdate())>= 0

--本周:

select * from t_news where (datepart(wk, addtime) = datepart(wk, getdate())) and (datepart(yy, addtime) = datepart(yy, getdate()))

--注意:此时不能用 datediff 差值为7,因为,datediff只表示间隔数

--本月:

select * from t_news where (datepart(yy, addtime) = datepart(yy, getdate())) and (datepart(mm, addtime) = datepart(mm, getdate()))

--最近一个月:

select * from t_news where (datepart(yy, addtime) = datepart(yy, getdate())) 
and ((datepart(mm, addtime) = datepart(mm, getdate())) or (31-datepart(dd,addtime)+datepart(dd, getdate()))<=31)

--本季度:

select * from t_news where datepart(qq, addtime) = datepart(qq, getdate()) and datepart(yy, addtime) = datepart(yy, getdate())