How many days from a given date to the first given day of week

The date of week calculation is not very easy.
here is a sample to calculate the first given day of week following (or equal to) a given date.
This is needed for instance when you want the first Tuesday in a month.

You have the following infos shown as variables, in the sample:
declare @givenDayOfWeek as int /* 1 for monday, 2 for tuesday...7 for sunday*/
declare @givenDate as datetime
declare @numberDaysFromGivenDateToFirstGivenDayOfWeek as integer

set [highlight]@numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7[/highlight]

Sample1 : find the date of the first Tuesday in May of year 2004

1-number of days from 1st of May 2004 to the first Tuesday

set @givenDate = '05/01/2004'
set @givenDayOfWeek = 2 /* [color red]2 for Tuesday[/color] */
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7 /* the formula gives 3 */

2-Searched date of the first Tuesday in May 2004

declare @searchedDate as datetime
@searchedDate = @givenDate + @numberDaysFromGivenDateToFirstGivenDayOfWeek /* it gives 05/04/2004 which is the first tuesday in May 2004 */

Sample2 : find the date of the Last Friday in May of year 2004

1-Determine the reference date

The last Friday is within the 7 last days of May, therefore it is the first friday following(or equal) to the 25th of May 2004 (31 - 6) [color red]This is the trick !![/color]

2-number of days from 25th of May 2004 to the first Friday

set @givenDate = '05/25/2004'
set @givenDayOfWeek = 5 /* [color red]5 for Friday[/color] */
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7 /* the formula gives 3 */

3-Searched date of the last Friday in May 2004

declare @searchedDate as datetime
@searchedDate = @givenDate + @numberDaysFromGivenDateToFirstGivenDayOfWeek /* it gives 05/28/2004 which is the last friday in May 2004 */

Additionnal trick

If in your query you have a lot of records to calculate, you can simplify the formula by setting the @@DATEFIRST parameter
You just have to reset the param when you've finished to prevent miscalculation elsewhere.

declare @originalDATEFIRST as int
set @originalDATEFIRST = @@DATEFIRST
set DATEFIRST @givenDayOfWeek /* [color red]that's the trick[/color] */
...
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate))%7 /*[highlight]Simplified formula[/highlight]*/
...
set DATEFIRST @originalDATEFIRST /* [color red]reset to original value[/color]*/
...