How to Count Occurrences of Specific Days Between Two Dates in TSQL?

Counting Occurrences of Specific Days between Dates in TSQL

When dealing with date-related calculations in TSQL, determining the frequency of specific weekdays can become a challenge. One such scenario involves calculating the number of "Tuesdays" between two given dates.

Solution

To count the number of instances of any day, including Tuesdays, you can utilize the provided code:

<code class="tsql">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</code>

In this code, you can substitute "Tuesday" with any other desired day by adjusting the corresponding day code (e.g., -6 for Tuesday).

By providing a start and end date, this code calculates the number of occurrences of each day within that period. It assumes a start day of Monday (code -7) and calculates the difference between the number of days that are multiples of 7 (e.g., multiples of days since Monday) between the start and end dates. This effectively counts the instances of each day within the specified date range.

The above is the detailed content of How to Count Occurrences of Specific Days Between Two Dates in TSQL?. For more information, please follow other related articles on the PHP Chinese website!