Calculating the Number of Tuesdays Between Two Dates in TSQL

Determining the number of Tuesdays between two dates is a common challenge in TSQL programming. One efficient approach is outlined by t-clausen.dk:

To calculate the instances of each weekday:

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

This query returns the number of occurrences of each day of the week within the specified date range. The calculations are based on the following principles:

• The datediff function calculates the difference between two dates in days.
• The expression -7 represents the Sunday before the start date.
• The expression -6 represents the Monday before the start date.
• The division by 7 groups the days by week.
• The subtraction of the number of days from the start date up to the Monday before the start date calculates the number of weeks between the start and end dates.
• The subtraction of the number of days from the end date up to the day of the week before the end date calculates the number of days left in the last week.

By applying this method, you can easily determine the number of Tuesdays or any other day of the week within a given date range.