Find the first and last date of any month given the Month in Number and Year

Recently, I had a requirement where I need to get the first and last date of any month provided that the Month number and Year is given. Here are some of the approaches

-- Input

DECLARE @Month int = 12, @Year int = 2012

--Approach 1:

SELECT  
		FirstDateOfMonth = CONVERT(VARCHAR(10), DATEADD(M,@Month-1,DATEADD(YY,@Year-1900,0)) ,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10), DATEADD(DD,-1,DATEADD(M,@Month,DATEADD(YY,@Year-1900,0))),110)

-- Approach 2:

DECLARE @StartDate DATETIME = CAST(@Month AS VARCHAR(2)) + '/' + '01/' + CAST(@Year AS VARCHAR(4)) -- Must be in MM/DD/YYYY format
SELECT 
		FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(DD,-DAY(@StartDate),DATEADD(M,1,@StartDate)),110)

-- Approach 3:

SELECT 
		FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(M, 1, CONVERT(DATETIME, CAST(@Month AS VARCHAR)+ '/01/' + CAST(@Year AS VARCHAR))) - 1,110)

-- Approach 4:

SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@StartDate)+1,0)),110)

-- Approach 5:

SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(MS, -3, DATEADD(MM, DATEDIFF(M, 0, @StartDate) + 1, 0)),110)

-- Approach 6:

SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10),DATEADD(DD, -1, DATEADD(MM, 1, @StartDate)),110)

--Approach 7: (Denali's EOMonth)

SELECT	FirstDateOfMonth = CONVERT(VARCHAR(10),@StartDate,110)
		,LastDateOfMonth = CONVERT(VARCHAR(10),EOMonth(@StartDate),110)

N.B.~ Suppose the Month Name is given instead of Month number e.g. JAN or FEB say. In that case we can convert to the month name to month number and proceed as shown under

SELECT MonthName = 'JAN', MonthNumber = DATEPART(MM,CAST('JAN'+ ' 1900' AS DATETIME))

OR form a kind of lookup table

;WITH MonthLookup AS
(
	SELECT MonthName = 'JAN', MonthNumber =  1  UNION ALL
	SELECT MonthName = 'FEB', MonthNumber =  2  UNION ALL
	SELECT MonthName = 'MAR', MonthNumber =  3  UNION ALL
	SELECT MonthName = 'APR', MonthNumber =  4  
)
SELECT MonthName,MonthNumber 
FROM MonthLookup
WHERE  MonthName = 'JAN'

Hope this will be helpful