sqlserver查询本周、上周、今天、昨天、本月、上月数据

最新推荐文章于 2024-08-06 17:03:59 发布

香煎三文鱼

最新推荐文章于 2024-08-06 17:03:59 发布

阅读量3.1k

点赞数 2

文章标签： sql查询本周上周今天等

本文链接：https://blog.csdn.net/qq_39569480/article/details/104515549

版权

--本周
select * from CompletScan where CONVERT(datetime,日期字段,101)>=DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, getdate()) ), 0) 
and CONVERT(datetime,日期字段,101)<DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, -1, getdate()) ), 7);
--SELECT * FROM tablename where datediff(week,日期字段,getdate())=0  --这一种会把上周天也加上，不建议使用

--上周
select * from CompletScan where CONVERT(datetime,日期字段,101)>=dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),0)) 
and CONVERT(datetime,日期字段,101)<dateadd(week,-1,DATEADD(week,DATEDIFF(week,0,getdate()),7));
--SELECT * FROM tablename where datediff(week,日期字段,getdate())=1 --不建议使用

--今天
SELECT * FROM CompletScan where DATEDIFF(day,CONVERT(datetime,日期字段,101),GETDATE())=0;

--昨天
SELECT * FROM CompletScan where DATEDIFF(day,CONVERT(datetime,日期字段,101),GETDATE())=1; 

--本月 
SELECT * FROM tablename where DATEDIFF(month,日期字段,GETDATE())=0

--上月 
SELECT * FROM tablename where DATEDIFF(month,日期字段,GETDATE())=1