根据我们输入的日期获取最后3个星期四的日期

I was trying to get the last three Thursday dates based on the date which I gave. I got it right if i insert a Tuesdays date but does not work for a Friday date. What i did was:

我试图根据我给出的日期得到最后三个星期四的日期。如果我插入一个星期二的约会但我不适用于星期五约会,我说得对。我做的是:

select dt from (select *,ROW_NUMBER() over (order by(d.dt)) as rn from  
(SELECT Top 4 CONVERT(varchar, DATEADD(Month, -1, '5/29/2012')+number,101) as dt 
FROM master..spt_values WHERE TYPE ='p' AND 
DATEDIFF(d,DATEADD(Month, -1, '5/29/2012'),'5/29/2012') >= number 
AND DATENAME(w,DATEADD(Month, -1, '5/29/2012')+number) = 'Thursday') as d ) 
as nw where nw.rn>1

and the output I get is 5/10/2012, 5/17/2012, 5/24/2012.

我得到的输出是5/10 / 2012,5 / 17 / 2012,5 / 24/2012。

But when I change the date to 5/18/2012 the output i get is

但当我将日期更改为5/18/2012时,我得到的输出是

04/26/2012, 05/03/2012, 05/10/2012

04/26 / 2012,05 / 03 / 2012,05 / 10/2012

But it should be 05/3, 05/10, 05/17.....What is wrong or is there any other method to do this?

但它应该是05 / 3,05 / 10,05 / 17 .....有什么问题,还是有其他方法可以做到这一点?

1 个解决方案

#1

Just replace DATEADD(Month, -1, with DATEADD(Week, -4, and it starts working for Fridays and Saturdays.

只需替换DATEADD(月,-1,与DATEADD(周,-4),它开始工作在星期五和星期六。

Explanation: If you subtract a Month it usually substracts 30 or 31 days, which adds an extra Thursday if the start date is a Friday, Saturday or (in the 31 days case a Sunday). After that the Top 4 cuts the last Thursday in the list.

说明:如果您减去一个月,它通常会减去30或31天,如果开始日期是星期五,星期六或(在31天的情况下是星期日),则会增加额外的星期四。之后,排名前4位的名单中的最后一个星期四。

Edit, to just select three values:

编辑,只选择三个值:

select * from (select *,ROW_NUMBER() over (order by(d.dt)) as rn from   
(
   SELECT CONVERT(varchar, DATEADD(Week, -3, '5/25/2012')+number,101) as dt  
   FROM master..spt_values WHERE TYPE ='p' 
   AND DATEDIFF(d,DATEADD(Week, -3, '5/25/2012'), DATEADD(Day,-1,'5/25/2012')) >= number  
   AND DATENAME(w,DATEADD(Week, -3, '5/25/2012') + number) = 'Thursday') as d
)
as nw

#1