Day of the month

I am trying to code a proceedure that will run every weekend. This process will run for a number of hours beginning at 0900 Saturday and ending at 2100 Sunday. However, on the 3rd weekend of the month, I need it run for a shorter time and to also skip some tables. What I have now to find the day of the month I need is

Begin
SET @.3rdSaturday = @.1stDayMonth
/*Loops until the date of the first Saturday(DayofWeek #7)
of the current month is found */
WHILE DATEPART(dw,@.3rdSaturday) <> 7

/*Adds 1 day to the first day of the month until it
reaches the date of the first Friday of the month */
SET @.3rdSaturday = DATEADD(d,1,@.3rdSaturday)
/*Adds 14 days to the first Friday of the month.
The end result is the 3rd Fridays date for the current month*/
SET @.3rdSaturday = DATEADD(d,14,@.3rdSaturday)
End

I am trying to find a better way to find the 3rd Saturday (or similar) of the month. I have also investigated DATENAME function but ran into the same problem. Also if I were using SQLDMOFreq_Monthly I could do it but I am trying to write all of this in T-SQL.

Any and all help is appreciated.

Akinja

Akinja-Earl:

Take a look at this article about establishing a calendar table:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Also, are you running on SQL Server 2005 or SQL Server 2000?

Dave

|||Thanks, I will.|||

If you prefer not to use a calendar table you can try something like:

set nocount on

declare @.dateOfMonth datetime
set @.dateOfMonth = '10/13/6'

declare @.monthString varchar (2)
set @.monthString = convert (varchar (2), month (@.dateOfMonth))
declare @.yearString varchar (4)
set @.yearString = convert (varchar (4), year (@.dateOfMonth))

/*
select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from small_iterator (nolock)
where iter <= 7
) a
where datepart (dw, sampleDate) = 7
*/

-- --
-- create an iterator if you don't already have one.
-- --
declare @.iterator table (iter integer not null)
insert into @.iterator values (1)
insert into @.iterator values (2)
insert into @.iterator values (3)
insert into @.iterator values (4)
insert into @.iterator values (5)
insert into @.iterator values (6)
insert into @.iterator values (7)

select sampleDate,
datepart (dw, sampleDate) as dayOfWeek,
datename (dw, sampleDate) as nameOfDay
from ( select @.monthString + '/' + convert (char(2), 14 + iter) + '/' + @.yearString as sampleDate
from @.iterator
) a
where datepart (dw, sampleDate) = 7

-- -
-- S A M P L E O U T P U T :
-- -

-- sampleDate dayOfWeek nameOfDay
-- - --
-- 10/21/2006 7 Saturday

|||

Thanks you for both suggestions. I like the calendar creation method since I can reuse it for other purposes. The iteration method will work with what I am working now since I am trying to get it done quickly.

Akinja