The task set before my team was to determine the first Tuesday ten days after the Contract Date. At first, we discovered it was a simple calculation, but then it was asked "what about twenty days out?" or "the first Thursday after?" It turned out that simple calculation would not extrapolate to these other scenarios. Therefore, we had to develop a means of calculating on the fly with Y days to acquire the X day of the week.

It becomes necessary at this point to find a pattern and then isolate the formula that would render this pattern consistently. It was realized that the actual pattern was not found in comparing the original date (referred to as StartDate for the remainder of this article) to the resulting day, but the StartDate + Y days compared to the desired day.

The following was a spreadsheet built out showing the StartDate+Y day and how much you have to add to this date to get the desired day of the week:

Now that we have a pattern, it is necessary to get the right formula to return these results. When we subtract any of the pairs of values, it will return values between six and negative six. Thus, it becomes necessary to subtract the results from 7 or add 1. In this case, to get the reverse patterns seen in the table, we will subtract.

As a basic example, the required formula could look like this using the pair of A and B values:

Algebraically:
7 – ( A – B )
7 – ( 1 – 1 )
7 – 0
7
SQL Code:
SELECT 7 - ( 1 – 1 )
7

This returns the right value, but we should try another value pair such as A = Monday and B = Wednesday just to be certain:

Algebraically:
7 – ( A – B )
7 – ( 2 – 4 )
7 – -2
9
SQL Code:
SELECT 7 - ( 2 – 4 )
9

Now we have a problem. The value should be 2. Thus, we need to get the modulus to render the value down to the correct number. We cannot put the modulus around the whole result as this would return a value between 0-6. We should be able to return the modulus for the subtraction and then subtract from 7 to get the final number.

Algebraically:
7 – [ ( A – B ) % 7 ]
7 – [ ( 2 – 4 ) % 7 ]
7 – [ -2 % 7 ]
7 – 5
2
SQL Code:
SELECT 7 - ( (2 - 4) % 7 )
9

In fact, if we do this in Excel, it WILL return the value of 2. It is in SQL that the result is, once again, 9. Technically, Excel is correct in that the next lowest value that is a multiple of 7 less than say the -1 is -7, the absolute difference between the two values is six. SQL, on the other hand, appears to perform this calculation on the multiple of 7 closest to zero and returns the value as a negative. This is illustrated in the following table:

This is easily resolved by adding seven before performing the modulus operation in SQL and we always end up with the correct values as follows:

Algebraically:
7 – [ ( A – B + 7 ) % 7 ]
7 – [ ( 2 – 4 + 7 ) % 7 ]
7 – [ 5 % 7 ]
7 – 5
2
SQL Code:
SELECT 7 - ( ( 2 - 4 + 7 ) % 7 )
2

So, the final query would look something like this (though it can easily be introduced as a User Defined Function as well):

SQL Code:
DECLARE @StartDate date
DECLARE @DaysOut tinyint
DECLARE @DesiredDayOfWeek tinyint
SET @StartDate = '20110813' --The starting date
SET @DaysOut = 4 --Number of days to count out (the Desired day is AFTER this count)
SET @DesiredDayOfWeek = 6 --The desired day of week
SELECT DATEADD( d,( @DaysOut +( 7 - ( 7 + DATEPART( weekday, DATEADD( d, @DaysOut, @StartDate ) ) - @DesiredDayOfWeek ) ) % 7 ), @StartDate )

This works consistently for any number of days and for any day of the week you need to return. If the desired effect is for the StartDate + Y to be inclusive of that day, then just reduce the value of Y by 1.