Thursday, August 13, 2009

This is used to find all dates of a year mentioned along with (n)th week day

DECLARE @month int
DECLARE @year INT
DECLARE @weekday int
DECLARE @whichday int
DECLARE @dt datetime

SET @year=2012
SET @month=1
SET @weekday=5
SET @whichday=5


WHILE @month <= 12
BEGIN

SET @dt=CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

IF(MONTH(DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt))=@month)
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),1
ELSE
SELECT DATEADD(WEEK,@whichday-2,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),2
END
ELSE
BEGIN
IF(MONTH(DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)))=@month)
SELECT DATEADD(WEEK,@whichday,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),3
ELSE
SELECT DATEADD(WEEK,@whichday-1,DATEADD(DAY,@weekday-DATEPART(DW,@dt),@dt)),4
END

SET @month = @month + 1
END

No comments: