The following code returns the date of the first Sunday of a given month in a given year. Explain why/how it works. For bonus points, find a better way to do it.
DECLARE @Month INT, @Year INT
SET @Month = 5
SET @Year = 2009
--Calculate first Sunday of given month in given year
DECLARE @BD SMALLDATETIME, @FIRST_SUNDAY SMALLDATETIME
SET @BD = CONVERT(datetime, cast(@Year as varchar)+'-'+cast(@Month as varchar)+'-01', 120)
SET @FIRST_SUNDAY = DATEADD(wk, DATEDIFF(wk,0, DATEADD(dd,6-DATEPART(DAY,@BD),@BD)),0)-1
For more bonus points, tell me what inputs cause this query to fail.
Hint: The following code fixes the bug.
IF DATEPART(m,@FIRST_SUNDAY) < DATEPART(m,@start_date)
SET @FIRST_SUNDAY = @FIRST_SUNDAY+7