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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

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

  1. #1
    Riaan Guest

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


    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

  2. #2
    Lenny Guest

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

    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.devx.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




  3. #3
    Riaan Guest

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


    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.devx.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

    >
    >



  4. #4
    Riaan Guest

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


    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.devx.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

    >>
    >>

    >



  5. #5
    Chris Hylton Guest

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


    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.devx.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
    >>>
    >>>

    >>

    >



  6. #6
    Riaan Guest

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


    Thanx guys, you've all been a great help. I got the SP right and it's working
    perfectly.

    I hope I can return the favour someday...



    Kind Regards,
    Riaan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center