How to calculate the number of “Tuesdays” between

问题:

I'm trying to figure out how to calculate the number of "Tuesdays" between two dates in TSQL?

"Tuesday"could be any value.

回答1:

Thank you t-clausen.dk, Saved me few days. To get no of instances of each day:

declare @from datetime= '3/1/2013' 
declare @to datetime  = '3/31/2013' 


select 
 datediff(day, -7, @to)/7-datediff(day, -6, @from)/7 AS MON,
 datediff(day, -6, @to)/7-datediff(day, -5, @from)/7 AS TUE,
 datediff(day, -5, @to)/7-datediff(day, -4, @from)/7 AS WED,
 datediff(day, -4, @to)/7-datediff(day, -3, @from)/7 AS THU,
 datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 AS FRI,
 datediff(day, -2, @to)/7-datediff(day, -1, @from)/7 AS SAT,
 datediff(day, -1, @to)/7-datediff(day, 0, @from)/7 AS SUN

回答2:

declare @from datetime= '9/20/2011' 
declare @to datetime  = '9/28/2011' 

select datediff(day, -6, @to)/7-datediff(day, -5, @from)/7

1. find the week of the first monday before the tuesday in @from.
2. find the week of the first monday after @to
3. subtract the weeks

回答3:

@t-clausen.dk & Andriy M as response to t-clausen.dks response and comments

The query uses the fact that 1900-01-01 was a monday. And 1900-01-01 is the date 0.

select dateadd(day,0,0)

The second parameter into the datediff-function is the startdate.

So you are comparing '1899-12-26' with your @to-date and '1899-12-26' is a tuesday

select datename(dw,dateadd(day, 0, -6)), datename(dw, '1899-12-26')

Same thing about the second date that uses the same fact.

As a matter of fact you can compare with any known tuesday and corresponding wednesday (that isnt in the date interval you are investigating).

declare @from datetime= '2011-09-19' 
declare @to datetime  = '2011-10-15' 

select  datediff(day, '2011-09-13', @to)/7-datediff(day, '2011-09-14', @from)/7 as [works]
        ,datediff(day, '2011-10-18', @to)/7-datediff(day, '2011-10-19', @from)/7 as [works too]
        ,datediff(day, '2011-09-27', @to)/7-datediff(day, '2011-09-28', @from)/7 as [dont work]

Basically the algorithm is "All Tuesdays minus all Wednesdays".

回答4:

Check out this question: Count work days between two dates

There are a few ways you can leverage the answer to that question for yours as well.