---------Script to Populate  Calender Table------------

DECLARE @TEMP VARCHAR(50) = '2014-01-01'
DECLARE @TEMP1 VARCHAR(50)= '2014-12-31'
;WITH Calender AS
(
			SELECT CAST(@TEMP AS DATETIME) StartDate
			UNION ALL
			SELECT StartDate + 1
			FROM Calender
			WHERE StartDate + 1 <= @TEMP1
)
SELECT * INTO #temp
FROM (
SELECT		    CAST(CONVERT(CHAR(8),CAST(StartDate AS DATETIME),112) AS INT) AS DateKey
		,   StartDate
		,   DATEPART(DW, StartDate)     AS DayNumberOfWeek
		,   DATENAME(DW, StartDate)     AS EnglishDayNameOfWeek
		,   DAY     (StartDate)	        AS DayNumberOfMonth
		,   DATEPART(DY, StartDate)     AS DayNumberOfYear
		,   DATEPART(WK, StartDate)     AS WeekNumberOfYear
		,   DATENAME(MM, StartDate)     AS EnglishMonthName
		,   MONTH   (StartDate)		AS MonthNumberOfYear
		,   DATEPART(QQ, StartDate)     AS CalendarQuarter
		,   YEAR    (StartDate)		AS CalendarYear
		,  (CASE WHEN MONTH(StartDate)>=1 AND MONTH(StartDate) <=6 THEN 1 ELSE 2 END) AS CalendarSemester
FROM Calender
	)x
OPTION (MAXRECURSION 0)

GO
--------Your Required Dates ------------------

DECLARE @AsofDate varchar(max) = '2014-05-10'
SELECT CONVERT(CHAR(128), MAX(StartDate), 6 ) AS Dates FROM #temp WHERE StartDate< = @AsofDate  GROUP BY MonthNumberOfYear

--------Drop table ---------------------------
DROP TABLE #temp
-----------------------------------------------