我想从存储插入记录的表中获取当前一周内插入的记录。
我尝试过:
SELECT PId
,WorkDate
,Hours
,EmpId
FROM Acb
WHERE EmpId=@EmpId AND WorkDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
我想从存储插入记录的表中获取当前一周内插入的记录。
我尝试过:
SELECT PId
,WorkDate
,Hours
,EmpId
FROM Acb
WHERE EmpId=@EmpId AND WorkDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE()
就这样做:
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()))
说明:
datepart(dw, getdate())
返回当前周的日期数字,从1到7,以您使用 SET DATEFIRST 指定的日期开始计算。dateadd(day, 1-datepart(dw, getdate()), getdate())
减去必要的天数以到达当前周的开始位置。CONVERT(date,getdate())
用于删除 GETDATE() 的时间部分,因为您想要从午夜开始的数据。SET DATEFIRST 1
会恢复到其原始状态。 - orBeat它对我有效。
Select * From Acb Where WorkDate BETWEEN DATEADD(DAY, -7, GETDATE()) AND DATEADD(DAY, 1, GETDATE())
你需要在AND子句后添加以下代码:AND DATEADD(DAY, 1, GETDATE())
datepart(dw, getdate())
是本周的当前日期,dateadd(day, 1-datepart(dw, getdate()), getdate())
应该是本周的第一天,加上7即可得到本周的最后一天。
您可以使用以下查询来提取当前周:
select datepart(dw, getdate()) as CurrentWeek
使用DATEDIFF
也可以,但有点hacky,因为它不关心datefirst
:
set datefirst 1; -- set monday as first day of week
declare @Now datetime = '2020-09-28 11:00';
select *
into #Temp
from
(select 1 as Nbr, '2020-09-22 10:00' as Created
union
select 2 as Nbr, '2020-09-25 10:00' as Created
union
select 2 as Nbr, '2020-09-28 10:00' as Created) t
select * from #Temp where DATEDIFF(ww, dateadd(dd, -@@datefirst, Created), dateadd(dd, -@@datefirst, @Now)) = 0 -- returns 1 result
select * from #Temp where DATEDIFF(ww, dateadd(dd, -@@datefirst, Created), dateadd(dd, -@@datefirst, @Now)) = 1 -- returns 2 results
drop table #Temp
SET DATEFIRST 1;
;With CTE
AS
(
SELECT
FORMAT(CreatedDate, 'MMMM-yyyy') as Months,
CASE
WHEN YEAR(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min(CreatedDate)), Min(CreatedDate))) < YEAR(Min(CreatedDate))
THEN FORMAT(DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0 ,GETDATE())), 0) ,'MMM dd') + ' - ' + FORMAT(DATEADD(dd, 7-(DATEPART(dw, Min(CreatedDate))), Min(CreatedDate)) ,'MMM dd')
ELSE
FORMAT(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min(CreatedDate)), Min(CreatedDate)) ,'MMM dd') + ' - ' + FORMAT(DATEADD(dd, 7-(DATEPART(dw, Min(CreatedDate))), Min(CreatedDate)) ,'MMM dd')
END DateRange,
Sum(ISNULL(Total,0)) AS Total,
sum(cast(Duration as int)) as Duration
FROM TL_VriandOPI_Vendorbilling where VendorId=@userID and CompanyId=@CompanyID
Group By DATEPART(wk, CreatedDate) ,FORMAT(CreatedDate, 'MMMM-yyyy')
)
SELECT Months,DateRange,Total,Duration,
case when DateRange=(select FORMAT(DATEADD(DAY, 1-DATEPART(WEEKDAY, Min(getdate())), Min(getdate())) ,'MMM dd') + ' - ' +
FORMAT(DATEADD(dd, 7-(DATEPART(dw, Min(getdate()))), Min(getdate())) ,'MMM dd'))
then 1 else 0 end as Thisweek
FROM CTE order by Months desc