Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    9

    Unanswered: How to find Second monday of october?

    Hi,

    Can any one help to get the date of second monday of october for the given year.

    Ex: the input will be 10, 2006

    and output should be 9, where 9 is the second monday date

  2. #2
    Join Date
    Jul 2003
    Location
    Romania
    Posts
    8

    Get the day of the second monday

    This may be a way to do that:

    create function dbo.second_monday_on_oct (@ra_year int)
    returns int
    as

    begin
    declare @full_date datetime
    declare @day int


    set @full_date=convert(smalldatetime, '1.10.'+cast(@ra_year as char(4)), 104)
    set @day= 16- datepart(dw, @full_date)

    if datepart(dw, @full_date)=1 set @day=@day-7
    if @@datefirst<>1 set @day =@day+ 8 - @@datefirst

    return @day
    end

    Best regards,
    Adriana

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    EDIT: very nice, adriana, you beat me to it, i was busy testing my solution while you posted yours

    but i can do it without an IF test by using modulo arithmetic

    here's my thought process:

    ----------------------------------

    okay, we solve this using simple logic

    if the 1st of october is a monday, the second monday is the 8th -- the point being that the second monday can't be any earlier than the 8th

    if the 1st of october is a tuesday, the second monday is the 14th -- the point being that the second monday can't be any later than the 14th

    so the second monday of october for any year is between the 8th and the 14th

    okay, so let's look at october 8th --

    if october 8th is a sun, then the 9th is the second monday of october
    if october 8th is a mon, then the 8th is the second monday of october
    if october 8th is a tue, then the 14th is the second monday of october
    if october 8th is a wed, then the 13th is the second monday of october
    if october 8th is a thu, then the 12th is the second monday of october
    if october 8th is a fri, then the 11th is the second monday of october
    if october 8th is a sat, then the 10th is the second monday of october

    okay so what do we have in SQL Server that tells us what day of the week a date is? answer: DATEPART(dw,date)

    i will not bother explaining how i derived the following formula, but i assure you it works

    Code:
    declare @year integer
    
    set @year = 2006
    
    select dateadd(day
              , ( 9 - datepart(dw
                      , cast(cast(@year as varchar)+'-10-08' as datetime)) ) % 7
              , cast(cast(@year as varchar)+'-10-08' as datetime)
            ) as secondmonday
    secondmonday is a datetime value for the 2nd monday of october for the given year

    to use this formula, all you have to do is plug whatever year you want into the @year parameter

    or, to make it a generic formula for whatever the current year is, you can simply use year(getdate()) instead of @year

    neat, eh?
    Last edited by r937; 07-02-06 at 13:00.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Posts
    9
    Thanks for your suggestion. Both the solutions are working fine for me.

    Now, one step ahead

    I want all the things to be parameterized

    that is now i wanted to provide 'October', '2' and 'Mon' as argument

    so the function will take the argument as

    function day_Cal(@year, @month, @whichWeek, @whichDay)
    returns @day

    That is need this to be generalized.

    Expecting for a favorite solution.

    Thanks in advance

    Bala

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •