# Thread: Calculaing Second Sunday of a given month

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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
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```

3. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Code:
```declare @dt datetime
from master..spt_values
where type='P'
and number between 8 and 14
Just replace getdate() with any date in the month for other months

4. Registered User
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

5. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369