How to calculate the number of "Tuesdays" between two dates in TSQL?

Question

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

"Tuesday"could be any value.

Solution 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

Solution 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

Solution 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".

Solution 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.

View more solutions

Comments

[t-clausen.dk]

The answers for that will be really hard to convert to this question. Also they are not really worthy of all the points they recieved. Better answers for that question here: stackoverflow.com/questions/6704905/…

[Andriy M]

+1 Works great. Can't crack the formula so far, though. It's clear that -6 and -5 could easily be 1 and 2 respectively or any other Tuesday and the corresponding (subsequent) Wednesday. What I'm wondering about is whether you got to the right figures empirically only or you somehow calculated them.

[t-clausen.dk]

@AndriyM yes, -6 and -5 yield the same result as 1 and 2 would do, that would make it harder to explain though. I reached the result by calculating and testing, didn't get the correct result the first few times.

[t-clausen.dk]

@arjmand if this solved your problem, please accept the answer

[Andriy M]

There's a good chance @arjmand wasn't noted about your comment, because I was. :) Anyway, he/she is not the author of the question and so can't accept your answer. And while I'm here, I'd like to say that I'm baffled by the stupidity of my former comment. I mean I'm appalled at how I mistook -6 and -5 to actually mean Tuesday and Wednesday, for they aren't dates really (that was probably what I mistook them for) but the numbers of days to subtract. Anyway, your answer is still great, cheers!

[t-clausen.dk]

@AndriyM it is a tricky syntax. I noticed you love those as well. Sad to see how unappriciated these algoritms are.