Have a look at example:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2013-01-01'
SET @enddate = '2013-05-01'
;WITH Months AS
(
SELECT @startdate AS MyDate
UNION ALL
SELECT DATEADD(mm,1,MyDate) AS MyDate
FROM Months
WHERE DATEADD(mm,1,MyDate) <= @enddate
)
SELECT CONVERT(VARCHAR(30), MyDate, 121) AS MyMonths, LEFT(DATENAME(MONTH ,MyDate),3) + CONVERT(VARCHAR(4),YEAR(MyDate)) AS MyMonths1
FROM Months
Result:
MyMonths MyMonths1
2013-01-01 00:00:00.000 Jan2013
2013-02-01 00:00:00.000 Feb2013
2013-03-01 00:00:00.000 Mar2013
2013-04-01 00:00:00.000 Apr2013
2013-05-01 00:00:00.000 May2013
Above query uses
CTE[
^], but it is possible to achieve that using
DO...WHILE[
^] loop.