q; How to write this function

How can I write a function that accepts a data as a parameter and if the date
is
1. Last Monday in May or
2. First Monday in September or
3. 4th Tuesday in November
returns true otherwise returns false.The best answer, if these are particular dates known ahead of time, is
a calendar table. But brute force works too.
declare @.d datetime
set @.d = '28 Nov 2006'
SELECT CASE WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 5
AND DATEPART(day,@.d) between 25 and 31
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 9
AND DATEPART(day,@.d) between 1 and 7
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Tuesday'
AND DATEPART(month,@.d) = 11
AND DATEPART(day,@.d) between 22 and 28
THEN 'True'
ELSE 'False'
END as Answer
Roy Harvey
Beacon Falls, CT
On Wed, 27 Dec 2006 04:43:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>How can I write a function that accepts a data as a parameter and if the date
>is
>1. Last Monday in May or
>2. First Monday in September or
>3. 4th Tuesday in November
>returns true otherwise returns false.|||JIM
Take a look at Steve Kass's function
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
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FED1D4FF-5F9E-414B-B115-2118EE32F4EC@.microsoft.com...
> How can I write a function that accepts a data as a parameter and if the
> date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>|||On Wed, 27 Dec 2006 07:18:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Thank you very much for your help, last one should be 4th Thursday (sorry my
>mistake), how would this change the code?
Well the fourth Thursday would be a lot like the fourth Tuesday,
wouldn't it? Just copy the day range from that one.
Roy|||Wow, I've been seeing homework assignments getting posted for 20 years now.
"JIM.H." wrote:
> How can I write a function that accepts a data as a parameter and if the date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>