How to find Second monday of october?

Hi,
Can any one help to get the date of second monday of october for the given year.
Ex: the input will be 10, 2006
and output should be 9, where 9 is the second monday dateThis may be a way to do that:

create function dbo.second_monday_on_oct (@.ra_year int)
returns int
as

begin
declare @.full_date datetime
declare @.day int

set @.full_date=convert(smalldatetime, '1.10.'+cast(@.ra_year as char(4)), 104)
set @.day= 16- datepart(dw, @.full_date)

if datepart(dw, @.full_date)=1 set @.day=@.day-7
if @.@.datefirst<>1 set @.day =@.day+ 8 - @.@.datefirst

return @.day
end

Best regards,
Adriana|||EDIT: very nice, adriana, you beat me to it, i was busy testing my solution while you posted yours :)

but i can do it without an IF test by using modulo arithmetic

here's my thought process:

-----------

okay, we solve this using simple logic

if the 1st of october is a monday, the second monday is the 8th -- the point being that the second monday can't be any earlier than the 8th

if the 1st of october is a tuesday, the second monday is the 14th -- the point being that the second monday can't be any later than the 14th

so the second monday of october for any year is between the 8th and the 14th

okay, so let's look at october 8th --

if october 8th is a sun, then the 9th is the second monday of october
if october 8th is a mon, then the 8th is the second monday of october
if october 8th is a tue, then the 14th is the second monday of october
if october 8th is a wed, then the 13th is the second monday of october
if october 8th is a thu, then the 12th is the second monday of october
if october 8th is a fri, then the 11th is the second monday of october
if october 8th is a sat, then the 10th is the second monday of october

okay so what do we have in SQL Server that tells us what day of the week a date is? answer: DATEPART(dw,date)

i will not bother explaining how i derived the following formula, but i assure you it works

declare @.year integer

set @.year = 2006

select dateadd(day
, ( 9 - datepart(dw
, cast(cast(@.year as varchar)+'-10-08' as datetime)) ) % 7
, cast(cast(@.year as varchar)+'-10-08' as datetime)
) as secondmondaysecondmonday is a datetime value for the 2nd monday of october for the given year

to use this formula, all you have to do is plug whatever year you want into the @.year parameter

or, to make it a generic formula for whatever the current year is, you can simply use year(getdate()) instead of @.year

neat, eh?|||Thanks for your suggestion. Both the solutions are working fine for me.

Now, one step ahead

I want all the things to be parameterized

that is now i wanted to provide 'October', '2' and 'Mon' as argument

so the function will take the argument as

function day_Cal(@.year, @.month, @.whichWeek, @.whichDay)
returns @.day

That is need this to be generalized.

Expecting for a favorite solution.

Thanks in advance

Bala