Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Calculaing Second Sunday of a given month

    Folks,
    Iam new to sql.I want to calculate the second sunday of a particular given month

    example-
    Given MOnth august - second sunday is 9
    I should get like this.....
    can anyone help me out with this...

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Calendar table!
    Code:
    CREATE TABLE dbo.calendar (
       the_date     datetime NOT NULL
     , is_monday    bit      NOT NULL
     , is_tuesday   bit      NOT NULL
     , is_wednesday bit      NOT NULL
     , is_thursday  bit      NOT NULL
     , is_friday    bit      NOT NULL
     , is_saturday  bit      NOT NULL
     , is_sunday    bit      NOT NULL
     , is_weekend As (is_saturday ^ is_sunday)
     , is_holiday   bit
     , holiday_desc varchar(50)
    )
    GO
    
    ALTER TABLE dbo.calendar
    ADD
       CONSTRAINT pk_calendar PRIMARY KEY CLUSTERED (the_date)
         WITH FILLFACTOR = 100
    GO
    
    INSERT INTO dbo.calendar (the_date, is_monday, is_tuesday, is_wednesday, is_thursday, is_friday, is_saturday, is_sunday)
    SELECT the_date
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 0 THEN 1 ELSE 0 END As is_monday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 1 THEN 1 ELSE 0 END As is_tuesday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 2 THEN 1 ELSE 0 END As is_wednesday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 3 THEN 1 ELSE 0 END As is_thursday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 4 THEN 1 ELSE 0 END As is_friday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 5 THEN 1 ELSE 0 END As is_saturday
         , CASE WHEN DateDiff(dd, 0, the_date) % 7 = 6 THEN 1 ELSE 0 END As is_sunday
    FROM   (
            SELECT DateAdd(dd, number, 0) As the_date
            FROM   (
                    SELECT (a.number * 256) + b.number As number
                    FROM 	 (
                            SELECT number
                            FROM   master..spt_values
                            WHERE  type = 'P'
                            AND    number <= 255
                           ) As a
                     CROSS
                      JOIN (
                            SELECT number
                            FROM   master..spt_values
                            WHERE  type = 'P'
                            AND    number <= 255
                           ) As b
                   ) As numbers
           ) As dates
    GO
    
    SELECT the_date
    FROM   (
            SELECT the_date
                 , Row_Number() OVER (ORDER BY the_date ASC) As row_num
            FROM   dbo.calendar
            WHERE  Month(the_date) = 8
            AND    Year(the_date) = 2009
            AND    is_sunday = 1
           ) As a_month_of_sundays
    WHERE  row_num = 2
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    declare @dt datetime
    select @dt=dateadd(mm,datediff(mm,'',getdate()),'')
    select dateadd(dd,number,@dt), datepart(dw,dateadd(dd,number,@dt))
    from master..spt_values 
    where type='P'
      and number between 8 and 14
      and datepart(dw,dateadd(dd,number,@dt))=1
    Just replace getdate() with any date in the month for other months

  4. #4
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    declare @dt datetime
    select @dt = '02/02/2009'

    select convert (datetime, substring (convert (varchar, @dt, 121), 1, 8) + '01', 121) -
    datepart (dw, convert (datetime, substring (convert (varchar, @dt, 121), 1, 8) + '01', 121) + 6) + 14
    MohammedU
    SQL Server MVP

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    To build on method by MohammedU
    Instead of converting between datetime and varchar

    select dateadd(mm,datediff(mm,'',@dt),'')
    -datepart(dw,dateadd(mm,datediff(mm,'',@dt),'')+6)+ 14

Posting Permissions

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