Hi Alex

Steve Kass has a similar UDF I have been using for some reports.

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

All very nice,  but how does this compare (both in performance and clarity ) to using a calendar table ?

Ive used Cte's to break down my thinking clearly but basically you are looking for a particular day between a set weekly period , ie the Wednesday between the 15th and 22nd of the month.

My table presently has the date (dte) and a DayBitMask(1=Monday,2=Tuesday,4 = Wednesday).

Obviously the beauty of a table is that if you need to regularly reference a calculated value you can add a column for it , and add an index.  Just doing Top 1 where > Earliest date caused an index scan,  'closing' the range makes a seek.

Declare @NthDayOfMonth integer

Set @NthDayOfMonth = 3;

with MonthList(FirstOfMonth)

as

(

  Select convert(smalldatetime,'20090101')

),

FirstPossibleDay(FirstPoss)

as

(

  Select FirstOfMonth+(7*(@NthDayOfMonth-1))

    from MonthList

)

,

SeekRange(FirstPoss,LastPoss)

as

(

  Select FirstPoss,FirstPoss+7

   from  FirstPossibleDay

)

Select Dte

from Calendar,SeekRange

where Dte between FirstPoss and LastPoss

 and daymask = 4

Sorry For the length of this post ,  it would appear that the tab version does appear to perform better.

Tab  Cpu = 125 Dur = 2264

Calc Cpu = 734 Dur = 2747

CREATE FUNCTION ComposeDate(@year INT, @month INT, @day INT)

-- I deliberately do not check if the parameters are valid

RETURNS TABLE AS RETURN(

 SELECT DATEADD(DAY, @day - 1, DATEADD(MONTH, @month - 1,  DATEADD(YEAR, @year - 1901, '19010101')))

   AS ComposedDate

)

GO

CREATE FUNCTION MthDayOfNthWeek(@year INT, @month INT, @week INT, @day INT)

-- @day 1 Sun, 2 Mon etc.

-- this function works in US but might need adjustments in other countries

RETURNS TABLE AS RETURN(

 SELECT t.ComposedDate AS MthDayOfNthWeek

   FROM (SELECT ComposedDate

     FROM ComposeDate(@year, @month, 1)) AS d

       CROSS APPLY ComposeDate(@year, @month,

       @day - DATEPART(dw,ComposedDate) + 1 +

       7*(@week + CASE WHEN DATEPART(dw,ComposedDate)>@day THEN 0 ELSE -1 END) ) AS t

)

GO

Drop Table Calendar

go

Create Table Calendar

(

Dte smalldatetime primary key,

Year  integer not null,

Month tinyint not null,

Day   tinyint  not null,

NThDayOfMonth tinyint not null,

DayOfWeek     tinyint not null

)

go

Create Unique index idxCalendar on Calendar(Year,Month,NThDayOfMonth,DayOfWeek )include(dte)

go

Declare @PopDate smalldatetime

Select @PopDate ='20000101'

while(@PopDate <='20501230') begin

  insert into Calendar values(@PopDate,DatePart(yy,@PopDate),DatePart(mm,@PopDate),DatePart(dd,@PopDate),

                                       (DatePart(dd,@PopDate)/7)+1,DatePart(dw,@PopDate))

  Select @PopDate =@PopDate+1

end

go

update statistics Calendar with fullscan

go

Create Function TabMthDayOfNthWeek(@year INT, @month INT, @week INT, @day INT)

RETURNS TABLE AS RETURN(

 Select dte from Calendar Where Year = @Year and Month =@Month and NthDayOfMonth = @week and DayOfWeek = @Day

)

go

select dte

 from sysobjects s1 cross apply

      sysobjects s2 cross apply

      tabMthDayOfNthWeek(2009, 6, 3, 4)

go

select MthDayOfNthWeek

 from sysobjects s1 cross apply

      sysobjects s2 cross apply

      MthDayOfNthWeek(2009, 6, 3, 4)

PingBack from http://www.pythian.com/news/3205/log-buffer-151-a-carnival-of-the-vanities-for-dbas

Faster ComposeDate. Only one function instead of three...

CREATE FUNCTION Readers.ComposeDate(@year SMALLINT, @month TINYINT, @day TINYINT)

RETURNS TABLE AS RETURN(

 SELECT DATEADD(MONTH, 12 * @Year - 22801 + @month, @day - 1) AS ComposedDate

) GO

Other faster calculations of LastDay

CREATE FUNCTION Readers.LastDayOfPreviousMonth(@year INT, @month INT)

RETURNS TABLE AS RETURN(

 SELECT DATEADD(MONTH, 12 * @Year - 22801 + @month, -1) AS LastDayOfPreviousMonth

) GO

CREATE FUNCTION Readers.LastDayOfMonth(@year INT, @month INT)

RETURNS TABLE AS RETURN(

 SELECT DATEADD(MONTH, 12 * @Year - 22800 + @month, -1) AS LastDayOfMonth

) GO