CODEWITHSUNDEEP
sqlt-sqldate
Voss Grose
Voss Grose

Reputation: 195

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

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

"Tuesday"could be any value.

Upvotes: 12

Views: 18391

Answers (4)

Arjmand
Arjmand

Reputation: 353

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

Upvotes: 20

t-clausen.dk
t-clausen.dk

Reputation: 44346

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

Upvotes: 13

Johan
Johan

Reputation: 1192

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

Upvotes: 7

Gibron
Gibron

Reputation: 1369

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.

Upvotes: 0

Related Questions