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

Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 1537
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 formatSELECT 		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 MonthLookupWHERE  MonthName = 'JAN'`