Well, that's because I have hardcoded the year in the code that I have posted to the current year:
i.e. SELECT @temp = DATEPART(dw, '11/1/01'),
@CurrYear = YEAR(GETDATE())

And this year it is 11/22.

Of course, in the actual code, the year is set to YEAR(@WhateverImLookingAt) and @CurrYear = YEAR(@WhateverImLookingAt).

If you change lines above, you get the 4th Thurs of November of any year.


>Maybe it's just me but your routine seems to always return 11/22.
>
>-Mike
>
>>I am trying to calculate the number of business days between 2 given dates and need to find the date for Thanksgiving using TSQL.
>>
>>I have the following code but was wondering if there was a better way.
>>
>>Here's what I have:
>>
>>DECLARE @temp as int,
>> @Thanks as smalldatetime,
>> @CurrYear as int
>>
>>SELECT @temp = DATEPART(dw, '11/1/01'),
>> @CurrYear = YEAR(GETDATE())
>>
>>SELECT @temp = @temp + 2
>>IF @temp > 7
>> BEGIN
>> SELECT @temp = @temp - 7
>> END
>>
>>SELECT @Thanks = CAST('11/' + CAST((29 - @temp) AS varchar(2)) + '/' + CAST(@CurrYear AS varchar(4)) as smalldatetime)
>>
>>
>>
>>TIA,
>>
>>Kim