|
|
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.
--Input Variables
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
--Out
put
SELECT @FIRST_SUNDAY
Update:
For more bonus points, tell me what inputs cause this query to fail.
Update 2:
Hint: The following code fixes the bug.
IF DATEPART(m,@FIRST_SUNDAY) < DATEPART(m,@start_date)
BEGIN
SET @FIRST_SUNDAY = @FIRST_SUNDAY+7
END
|
|
|
