如何仅在SQL服务器中获取当前周的数据? [英] how to get data of current week only in SQL server?
本文介绍了如何仅在SQL服务器中获取当前周的数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试过:
SELECT PID
,WorkDate
,Hours
,EmpId
FROM Acb
WHERE EmpId = @EmpId和工作日期DATEADD(DAY,-7,GETDATE())和GETDATE()
解决方案
这样做:
SET DATEFIRST 1 - 将周的开头定义为星期一
SELECT [...]
AND WorkDate> = dateadd(day,1-datepart(dw,getdate()),CONVERT(date,getdate()))
AND WorkDate& dateadd(day,8-datepart(dw,getdate()),CONVERT(date,getdate()))
说明:
-
datepart(dw,getdate())
将返回当前星期的数量,从1到7,从您使用 SET DATEFIRST 。 -
dateadd(day,1-datepart(dw,getdate()),getdate())
减去达到本周开始的必要天数 -
CONVERT(date,getdate())
用于删除GETDATE()的时间部分,因为您希望数据从午夜开始。
I want records from table which stores the current date when a record is inserted with in current week only.
I have tried:
SELECT PId
,WorkDate
,Hours
,EmpId
FROM Acb
WHERE EmpId=@EmpId AND WorkDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
解决方案
Do it like this:
SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT [...]
AND WorkDate >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate()))
AND WorkDate < dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
Explanation:
datepart(dw, getdate())
will return the number of the day in the current week, from 1 to 7, starting with whatever you specified using SET DATEFIRST.dateadd(day, 1-datepart(dw, getdate()), getdate())
subtracts the necessary number of days to reach the beginning of the current weekCONVERT(date,getdate())
is used to remove the time portion of GETDATE(), because you want data beginning at midnight.
这篇关于如何仅在SQL服务器中获取当前周的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文