Find dates for a particular day for a given year and month + Sql Server 2005

Question

Given a month and year, how do I know the dates for a day in that month.

e.g. Given month = Feb, year = 2010. I need to find the sunday dates in the month

The expected output is:

07/02/2010
14/02/2010
21/02/2010
28/02/2010

How to do so?

Answer 1

Have a look at something like

DECLARE @Month VARCHAR(3),
        @Year INT

SELECT  @Month = 'Feb',
        @Year = 2010

DECLARE @RunDate DATETIME

SELECT @RunDate = '01 ' + @Month + CAST(@Year AS VARCHAR(4))

;WITH DATES AS(
        SELECT  @RunDate DateVal,
                DATENAME(dw, @RunDate) DateNameVal
        UNION ALL
        SELECT  DateVal + 1,
                DATENAME(dw, DateVal + 1)
        FROM Dates
        WHERE DATEPART(month,DateVal) = DATEPART(month,DateVal + 1)
)
SELECT  *
FROM    DATES
WHERE   DateNameVal = 'Sunday'

Answer 2

To go along with @astander's answer, here is a link that shows some alternatives: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126721