# Thread: How to find Second monday of october?

1. Registered User
Join Date
Apr 2004
Posts
9

## Unanswered: 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 date

2. Registered User
Join Date
Jul 2003
Location
Romania
Posts
8

## Get the day of the second monday

This 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,

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

Code:
```declare @year integer

set @year = 2006

, ( 9 - datepart(dw
, cast(cast(@year as varchar)+'-10-08' as datetime)) ) % 7
, cast(cast(@year as varchar)+'-10-08' as datetime)
) as secondmonday```
secondmonday 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?
Last edited by r937; 07-02-06 at 12:00.

4. Registered User
Join Date
Apr 2004
Posts
9
Thanks for your suggestion. Both the solutions are working fine for me.

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.