trying to get the Date of the 3rd Sunday in February for any year in a Stored Pr

Thanx for the Help Lenny,

Your code already surpasses my comprehension of dates in SQL or Any language.
I'm definitely a step closer than I originally was and I appreciate it enormously...
but...

This method doesn't return consistent data. I have tested it and it works
fine until the year 2008 and also only for 3rd Sun in Feb.
Later than that, it misses a week and instead gives me the date a week earlier.

It also gives me wednesdays when I ask for Mondays etc.

This is what I need to pass and get returned:
I need to pass the SP: a Year (2006), a WeekNum (3), a WeekDay (7) and a
Month (3)

This should then accurately supply me with the date: "19 March 2006" (3rd
Sunday for March 2006)

This should work with any combination of parameters (Which your previous
code did'nt do unfortunately).

I'm sorry to be a nuisance, but I'm really knackered with this one.

Help me if you can. I Have included My SP Code in case it was something I
screwed up (Which is quite possible):

========================================================================
CREATE PROCEDURE [dbo].[GetDateFromCombination]
@TheDate datetime,
@TheWeekNum int,
@TheWeekDay int,
@TheMonth int,
@ReturnDate datetime OUTPUT

AS

SET NOCOUNT ON

DECLARE @foy DATETIME --First of the year
DECLARE @days SMALLINT --Number of days in the year
DECLARE @d SMALLINT --Day Counter

--SET @TheMonth = 2 --Feb
--SET @TheWeekDay = 1 --Sun
--SET @TheWeekNum = 3 --3rd Week

SET @foy = @TheDate - datepart(dy, @TheDate) + 1
--print @foy
SET @days = datediff(d, @foy, cast('12-31-'+cast(year(@foy) as varchar) as
datetime))
--print @days

CREATE TABLE #dates (
_date datetime,
fom datetime --First of the month
)

SET @d=0
WHILE @d <= @days
BEGIN
INSERT #dates
VALUES (@foy+@d,
cast(cast(month(@foy+@d) as varchar)+'-1-'+cast(year(@foy+@d)
as
varchar) as datetime)
)
SET @d=@d+1
END

SELECT @ReturnDate = Convert(Varchar(50),_date,106)
FROM #dates
WHERE datepart(m, _date) = @TheMonth
and datepart(dw, _date) = @TheWeekDay
and datepart(wk, _date) - datepart(wk, fom) + 1 = @TheWeekNum
Return(0)

DROP TABLE #dates
GO
==========================================================================

Kind Regards,
Riaan

"Lenny" <lenny@lenny.com> wrote:
>Hello Riaan.
>This seems to work (putting all dates in a table and simply selecting what
>you need):
>
>
>DECLARE @foy DATETIME --First of the year
>DECLARE @days SMALLINT --Number of days in the year
>DECLARE @d SMALLINT
>
>SET @foy = getdate() - datepart(dy, getdate()) + 1
>--print @foy
>SET @days = datediff(d, @foy, cast('12-31-'+cast(year(@foy) as varchar)
as
>datetime))
>--print @days
>
>CREATE TABLE #dates (
> _date datetime,
> fom datetime --First of the month
>)
>
>SET @d=0
>WHILE @d <= @days
>BEGIN
> INSERT #dates
> VALUES (@foy+@d,
> cast(cast(month(@foy+@d) as varchar)+'-1-'+cast(year(@foy+@d)
as
>varchar) as datetime)
> )
> SET @d=@d+1
>END
>
>SELECT _date, datepart(wk, _date) - datepart(wk, fom) + 1 AS _week
>FROM #dates
>WHERE datepart(m, _date) = 2 --February
>and datepart(dw, _date) = 7 --Sunday
>and datepart(wk, _date) - datepart(wk, fom) + 1 = 3 --Third occurrence
>
>DROP TABLE #dates
>
>
>Regards,
>Lenny
>
>
>"Riaan" <R144N@msn.com> wrote in message
>news:3ded2644$1@tnews.web.dev-archive.com...
>>
>> Hi there,
>>
>> I'm trying to get the Date of the 3rd Sunday in February for any year
in
>> a Stored Proc but to no avail.
>>
>> I have tried various ways of using DatePart, DateDiff etc. But alas!
>>
>> I.e:
>> The 3rd Sunday of Feb 2003 is 15 Feb 2003 (for example)
>> I need the 3rd Sunday of Feb for 2004 and 2005 as time goes on.
>>
>> Heeeeeeelp...
>>
>> Thanx,
>> R144N
>
>

Riaan,

Don't have SQL Server setting in front of me at my client site...but the
following VB example can be replicated over in SQL Server, all the same date
functions exist (even w/ the same name) so you could do the same logic, passing
the YEAR that you want to a UDF in SQL Server. You'll have to modify the
values like 'w' and 'ww' because I think they are slightly different in SQL
Server...check out the DatePart function in SQL Server, the constants are
in the BOL help. You'll also want to make sure the default for your SQL
Server install has the first day of the week set to Sunday, if it's not,
you'll have to modify the logic below accordingly, there is a SET statement
to do this...don't recall the system parameter, but it's there somewhere.

Here you go...

Dim dteFeb1st As Date
Dim strYear As String
Dim iDayOfWeek As Integer
Dim dteFirstSunday As Date
Dim dteThirdSunday As Date

strYear = "2002"
dteFeb1st = CDate("02/01/" & strYear)
iDayOfWeek = DatePart("w", dteFeb1st) 'get the weekday
If iDayOfWeek = 1 Then
dteFirstSunday = dteFeb1st
Else
dteFirstSunday = DateAdd("d", 8 - iDayOfWeek, dteFeb1st)
End If
dteThirdSunday = DateAdd("ww", 2, dteFirstSunday)

Have fun,
Chris

"Riaan" <R144N@msn.com> wrote:
>
>Ok,
>
>I've found the inconsistency and your code DOES work perfectly in the sense
>that it returns the 3rd week in Feb which might not always be the 3rd Wednesday
>(for a better example) in Feb
>
>There is sometimes that Blank bit in the beginning of the month in a calendar,
>so while it IS the 3rd week, it is actually the 2nd Wednesday
>
>Any thoughts to how this might be corrected on the fly?
>
>Kind Regards,
>Riaan
>
>"Riaan" <R144N@msn.com> wrote:
>>
>>Thanx for the Help Lenny,
>>
>>Your code already surpasses my comprehension of dates in SQL or Any language.
>>I'm definitely a step closer than I originally was and I appreciate it
enormously...
>>but...
>>
>>This method doesn't return consistent data. I have tested it and it works
>>fine until the year 2008 and also only for 3rd Sun in Feb.
>>Later than that, it misses a week and instead gives me the date a week
earlier.
>>
>>It also gives me wednesdays when I ask for Mondays etc.
>>
>>This is what I need to pass and get returned:
>>I need to pass the SP: a Year (2006), a WeekNum (3), a WeekDay (7) and
a
>>Month (3)
>>
>>This should then accurately supply me with the date: "19 March 2006" (3rd
>>Sunday for March 2006)
>>
>>This should work with any combination of parameters (Which your previous
>>code did'nt do unfortunately).
>>
>>I'm sorry to be a nuisance, but I'm really knackered with this one.
>>
>>Help me if you can. I Have included My SP Code in case it was something
>I
>>screwed up (Which is quite possible):
>>
>>========================================================================
>>CREATE PROCEDURE [dbo].[GetDateFromCombination]
>> @TheDate datetime,
>> @TheWeekNum int,
>> @TheWeekDay int,
>> @TheMonth int,
>> @ReturnDate datetime OUTPUT
>>
>>AS
>>
>>SET NOCOUNT ON
>>
>>DECLARE @foy DATETIME --First of the year
>>DECLARE @days SMALLINT --Number of days in the year
>>DECLARE @d SMALLINT --Day Counter
>>
>>
>>--SET @TheMonth = 2 --Feb
>>--SET @TheWeekDay = 1 --Sun
>>--SET @TheWeekNum = 3 --3rd Week
>>
>>
>>SET @foy = @TheDate - datepart(dy, @TheDate) + 1
>>--print @foy
>>SET @days = datediff(d, @foy, cast('12-31-'+cast(year(@foy) as varchar)
>as
>>datetime))
>>--print @days
>>
>>CREATE TABLE #dates (
>> _date datetime,
>> fom datetime --First of the month
>>)
>>
>>SET @d=0
>>WHILE @d <= @days
>>BEGIN
>> INSERT #dates
>> VALUES (@foy+@d,
>> cast(cast(month(@foy+@d) as varchar)+'-1-'+cast(year(@foy+@d)
>>as
>>varchar) as datetime)
>> )
>> SET @d=@d+1
>>END
>>
>>
>>SELECT @ReturnDate = Convert(Varchar(50),_date,106)
>>FROM #dates
>>WHERE datepart(m, _date) = @TheMonth
>>and datepart(dw, _date) = @TheWeekDay
>>and datepart(wk, _date) - datepart(wk, fom) + 1 = @TheWeekNum
>>Return(0)
>>
>>DROP TABLE #dates
>>GO
>>==========================================================================
>>
>>Kind Regards,
>>Riaan
>>
>>
>>"Lenny" <lenny@lenny.com> wrote:
>>>Hello Riaan.
>>>This seems to work (putting all dates in a table and simply selecting
what
>>>you need):
>>>
>>>
>>>DECLARE @foy DATETIME --First of the year
>>>DECLARE @days SMALLINT --Number of days in the year
>>>DECLARE @d SMALLINT
>>>
>>>SET @foy = getdate() - datepart(dy, getdate()) + 1
>>>--print @foy
>>>SET @days = datediff(d, @foy, cast('12-31-'+cast(year(@foy) as varchar)
>>as
>>>datetime))
>>>--print @days
>>>
>>>CREATE TABLE #dates (
>>> _date datetime,
>>> fom datetime --First of the month
>>>)
>>>
>>>SET @d=0
>>>WHILE @d <= @days
>>>BEGIN
>>> INSERT #dates
>>> VALUES (@foy+@d,
>>> cast(cast(month(@foy+@d) as varchar)+'-1-'+cast(year(@foy+@d)
>>as
>>>varchar) as datetime)
>>> )
>>> SET @d=@d+1
>>>END
>>>
>>>SELECT _date, datepart(wk, _date) - datepart(wk, fom) + 1 AS _week
>>>FROM #dates
>>>WHERE datepart(m, _date) = 2 --February
>>>and datepart(dw, _date) = 7 --Sunday
>>>and datepart(wk, _date) - datepart(wk, fom) + 1 = 3 --Third occurrence
>>>
>>>DROP TABLE #dates
>>>
>>>
>>>Regards,
>>>Lenny
>>>
>>>
>>>"Riaan" <R144N@msn.com> wrote in message
>>>news:3ded2644$1@tnews.web.dev-archive.com...
>>>>
>>>> Hi there,
>>>>
>>>> I'm trying to get the Date of the 3rd Sunday in February for any year
>>in
>>>> a Stored Proc but to no avail.
>>>>
>>>> I have tried various ways of using DatePart, DateDiff etc. But alas!
>>>>
>>>> I.e:
>>>> The 3rd Sunday of Feb 2003 is 15 Feb 2003 (for example)
>>>> I need the 3rd Sunday of Feb for 2004 and 2005 as time goes on.
>>>>
>>>> Heeeeeeelp...
>>>>
>>>> Thanx,
>>>> R144N
>>>
>>>
>>
>