Find a Day From a Sequenced Weekday in a Selected Month



DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)

INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
SELECT 2010,'Jan','Sun',2 UNION ALL
SELECT 2005,'Jan','Mon',3 UNION ALL
SELECT 1995,'Feb','Sun',1 UNION ALL
SELECT 2000,'Feb','Wed',4 UNION ALL
SELECT 1982,'Mar','Tue',2 

;with mycte as
(
select Yr, Mon, Dy, Dyno, 
CAST(Mon +'1 '+ cast(Yr as char(4)) as datetime) DayOne, 
datepart(weekday,CAST(Mon +'1 '+ cast(Yr as char(4)) as datetime)) as WeekNumDayOne, 
CASE Dy
WHEN  'Sun' THEN 1
WHEN  'Mon' THEN 2  
WHEN  'Tue' THEN 3
WHEN  'Wed' THEN 4
WHEN  'Thu' THEN 5
WHEN  'Fri' THEN 6
WHEN  'Sat' THEN 7
END WeekNum
FROM @tbl)

SELECT Yr, Mon, Dy, Dyno
,dateadd(day, (Dyno-case when WeekNumDayOne>WeekNum THEN 0 Else 1 END)*7-(WeekNumDayOne - WeekNum), DayOne) as dt
FROM mycte
Order by yr
/*

Yr	Mon	Dy	Dyno	dt
1982	Mar	Tue	2	1982-03-09 00:00:00.000
1995	Feb	Sun	1	1995-02-05 00:00:00.000
2000	Feb	Wed	4	2000-02-23 00:00:00.000
2005	Jan	Mon	3	2005-01-17 00:00:00.000
2010	Jan	Sun	2	2010-01-10 00:00:00.000

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/dbfcdeb0-0715-461a-a7c8-49ecee77518f
*/
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s