Finding the first, second or third monday in a month.

[Robert Johnson]

To handle some recurrning appointments, I need to be able to find the 1st
monday of the month, or the the 3rd monday of the month. How can I do that
for a particular date and year?

Robert

[Russell Fields]

Robert,

Using DATEPART and DATEADD you should be able to figure this out.

Take your base date and set it to the first day of the month.
SET @MyDayOneDate = DATEADD (dd, (DATEPART(dd,MyDate) * -1) +1, MyDate)

With that you can determine what day of the week is the first day of the
month
SET @FirstWeekDayOfMonth = DATEPART(dw, @MyDayOneDate)

From that you can then determine what the date is of the first Monday.
(Assuming that Sunday is DOW 1).
SET @FirstMonday = 1 + ((ABS(@FirstWeekDayOfMonth - 7) + 2) % 7)

The math figures out how much to add to the first day of the month (0-6) to
be Monday. (Or you could use a CASE statement.)

Now, of course, you can build this all into a single statement.

Once you know the first Monday, then figuring the remaining Mondays is
trivial.

Russell Fields

"Robert Johnson" <but...@internetwebzone.com> wrote in message
news:ej9hGyZ8...@TK2MSFTNGP10.phx.gbl...

[Aaron Bertrand [MVP]]

Here's one way. First, create a numbers table with 31 rows.

SET NOCOUNT ON

CREATE TABLE nums
(
offset TINYINT
)
GO

DECLARE @i TINYINT
SET @i = 0
WHILE @i <= 30
BEGIN
INSERT nums VALUES(@i)
SET @i = @i + 1
END

DECLARE @year INT,
@month INT,
@dayname VARCHAR(8),
@ordinal TINYINT,
@firstday SMALLDATETIME

-- so let's say you want the first Monday in March:

SET @year = 2003
SET @month = 3
SET @dayname = 'Monday'
SET @ordinal = 1

SET @firstday = CONVERT
(
SMALLDATETIME,
CAST(@year AS VARCHAR(4))
+'-'+CAST(@month AS VARCHAR(2))
+'-01'
)

SELECT rownum = IDENTITY(INT,1,1),
thedate = DATEADD(day, offset, @firstday)
INTO #newdates
FROM nums WHERE
datename(dw,
DATEADD(day, offset, @firstday)) = @dayname
ORDER BY offset

SELECT thedate FROM #newdates WHERE rownum = @ordinal

DROP TABLE #newdates

-- or if you wanted the 4th Sunday in June of 1999:

SET @year = 1999
SET @month = 6
SET @dayname = 'Sunday'
SET @ordinal = 4

-- Exact same code, except for the name of the #table:

SET @firstday = CONVERT
(
SMALLDATETIME,
CAST(@year AS VARCHAR(4))
+'-'+CAST(@month AS VARCHAR(2))
+'-01'
)

SELECT rownum = IDENTITY(INT,1,1),
thedate = DATEADD(day, offset, @firstday)
INTO #newdates2
FROM nums WHERE
datename(dw,
DATEADD(day, offset, @firstday)) = @dayname
ORDER BY offset

SELECT thedate FROM #newdates2 WHERE rownum = @ordinal

DROP TABLE #newdates2
DROP TABLE nums

Now, if you want the 1st and 2nd monday to be returned in the same
resultset, you'll have to pass the ordinal parameter(s) a little
differently. Or you could return the whole #newdates table to the
application and let it decide which row(s) to show.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.

"Robert Johnson" <but...@internetwebzone.com> wrote in message
news:ej9hGyZ8...@TK2MSFTNGP10.phx.gbl...

[Steve Kass]

Robert,

Here's one solution:

create function dbo.NthWeekDay(
@first datetime, -- First of the month of interest (no time part)
@nth tinyint, -- Which of them - 1st, 2nd, etc.
@dow tinyint -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @nth is too large

declare @result datetime
set @result = @first + 7*(@nth-1)
return @result + (7 + @dow - datepart(weekday,@result))%7
end
go

-- Find the 5th Thursday of August, 2002
select dbo.NthWeekDay('2002/08/01',5,5) as D

select datename(weekday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWeekDay('2002/08/01',5,5) as D
) X
go

drop function NthWeekDay

Steve Kass
Drew University

[Andrew John]

Robert,

Do you have a calendar table ? Simple one shown below,
but can be extended by holidays, workhours etc:

create table Calendar
(
TheDate datetime,
)
go

declare @Dt datetime
Set @Dt = '2002-01-01'

while @Dt < '2004-01-01'
begin
insert Calendar values ( @Dt )
set @Dt = @Dt + 1
end

If so an answer is:

declare @CurDate datetime
set @CurDate = current_timestamp -- Has hours/minutes etc, but can ignore
-- 3rd Monday must be at least 14 days from start of month
select min(TheDate) as ThirdMonday
from Calendar
where TheDate >= dateadd(d, 15 - datepart(d, @CurDate), @CurDate)
and datepart( dw, TheDate ) = ( 9 - @@datefirst ) % 7 -- In case not default datefirst

-- 1st Monday
select min(TheDate) as FirstMonday
from Calendar
where TheDate >= dateadd(d, 1 - datepart(d, @CurDate), @CurDate)
and datepart( dw, TheDate ) = ( 9 - @@datefirst ) % 7 -- In case not default datefirst

The algo is: Get to beginning of month. Add on however many weeks ( 2 x 7 + 1, or 0 x 7 + 1 ),
Look for next Monday. The 9 - ... %7 is just in case someone has messed with the
default 1st day of the week.

Regards
AJ

"Robert Johnson" <but...@internetwebzone.com> wrote in message
news:ej9hGyZ8...@TK2MSFTNGP10.phx.gbl...