我有一个查询,我需要,作为日期标准,两个月前结束的一周。
因此,例如,如果我在周一运行查询(截至目前,最后一个星期一是2016年2月1日),它将在2015年11月29日至2015年12月5日(周日至周六)进行查看。
然后下周,如果我运行它,它将集中在2015年12月6日至2015年12月12日(周日至周六)。
但是我需要它返回这个完全相同的日期范围,无论我在哪个工作日运行它。 例如,如果我在2016年2月1日到2016年2月5日(周一至周五)运行它,则会选择2015年11月29日至2015年12月5日的日期范围。
我不确定最好的办法是什么。 我已经考虑过以某种方式试图找到下一个星期六,然后在几周内将其计时,但在dateadd()中似乎没有一周选项。
I have a query where I need, as date criteria, the week ending two months prior.
So for example if I ran the query on Monday (as of right now, the last Monday was 2/1/2016), it would look at 11/29/2015 through 12/5/2015 inclusive (Sunday through Saturday).
And then next week if I ran it, it would focus on 12/6/2015 through 12/12/2015 (Sunday through Saturday).
However I need it to return this exact same date range no matter which weekday of the week I run it. So for example the date range 11/29/2015 through 12/5/2015 would be selected if I ran it on 2/1/2016 through 2/5/2016 (Mon-Fri).
I'm not sure what the best way is to go about this. I've considered somehow trying to find the next Saturday and then clocking that back a few weeks, but there doesn't seem to be a week option in dateadd().
最满意答案
要从周日到周六获得一周的第一个工作日:
FirstWeekDate = DateAdd("d", 1 - Weekday(Date()), Date())要回去,说8周:
EightWeeksBack = DateAdd("ww", -8, FirstWeekDate)然后,您可以添加/减去天数以获取间隔,例如。
EightWeeksBackLast = DateAdd("d", 6, DateAdd("ww", -8, FirstWeekDate))To get the first weekday of a week from Sunday to Saturday:
FirstWeekDate = DateAdd("d", 1 - Weekday(Date()), Date())To go back, say 8 weeks:
EightWeeksBack = DateAdd("ww", -8, FirstWeekDate)Then you can just add/subtrack days to get your intervals, for example.
EightWeeksBackLast = DateAdd("d", 6, DateAdd("ww", -8, FirstWeekDate))更多推荐
发布评论