use pubs
go
if exists (SELECT * FROM sysobjects where name='my_date' and type='U')
DROP TABLE my_date
go
SET NOCOUNT ON
CREATE TABLE my_date (Col1 datetime)
go
INSERT INTO my_date
VALUES (CONVERT(char(10), GETDATE(), 102))
select * from my_date
drop table my_date
go
if exists (SELECT * FROM sysobjects where name='Records' and type='U')
DROP TABLE Records
go
CREATE TABLE Records
(
Record_number int ,
Entered_on datetime
)
GO
-- Add 700 days of data - 350 on each side of today
DECLARE @mydatetime datetime,@i smallint
SELECT @mydatetime=getdate(),@i=-350
-- Insert 700 rows
WHILE (@i <= 700)
BEGIN
INSERT Records VALUES (@i,DATEADD(day,@i,@mydatetime) )
SELECT @i=@i+1
END
go
-- Find the 2nd tuesday of each month within 48 weeks of today
SELECT Record_number, Entered_on
FROM Records
WHERE
DATEPART(WEEKDAY, Entered_on)=3
-- Tuesday is 3rd day of week (in USA)
AND
DATEPART(DAY, Entered_on) BETWEEN 8 AND 14
-- The 2nd week is from the 8th to 14th
AND
DATEDIFF(WEEK, Entered_on, GETDATE()) <= 48
-- Within 48 weeks of today.
GO
DROP TABLE #Records
