Use CTE to Get List of Month

We can use CTE (common table expression) to get a list of month.

 

;WITH DateYear AS
(
SELECT 0 AS num
UNION ALL
SELECT num + 1 FROM DateYear
WHERE num < 11
)
SELECT CONVERT(DATE,DATEADD(MONTH,(num-13),convert(datetime, '20140401', 110))) AS MyMonth from DateYear

 

If you have multipel CTE, use comma to seperate them.

;WITH SomeClause1 AS
(
  SELECT ....
)
, SomeClause2 AS
(
  SELECT ....
)

 

Reference:

1. http://www.sqlusa.com/bestpractices/datetimeconversion/

2. http://consultingblogs.emc.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx