/*生成一个日期范围，如2014.01、2014.02...@Increment 步进值@StartDate 开始日期@EndDate   结束日期----------------------@SelectedRange 返回值名称（多语句表值函数的返回值必须存放在返回值变量中）    返回表的字段和类型：    iDate DATETIME,    iYear char(4),    iMonth char(2),    iYM CHAR(7)*/CREATE FUNCTION [dbo].[DateRange](    @Increment char(1) = ‘m‘,    @StartDate    DATE=‘20140101‘,    @EndDate    DATE=null)RETURNS @SelectedRange TABLE(    iDate DATE,    iYear char(4),    iMonth char(2),    iYM CHAR(7))ASBEGIN/*Default value for stored procedures parameter have to be constants. The function GETDATE() returns a different value from time to time, so it is not a constant expression.*/--IF @EndDate is null--SET @EndDate = getdate()--orSET @EndDate=Coalesce(@EndDate,GetDate())        ;WITH cteRange(DateRange) AS        (            SELECT CAST(@StartDate AS DATE)            UNION ALL            SELECT                   CASE                        WHEN @Increment = ‘d‘ THEN DATEADD(dd, 1, DateRange)                        WHEN @Increment = ‘w‘ THEN DATEADD(ww, 1, DateRange)                        WHEN @Increment = ‘m‘ THEN DATEADD(mm, 1, DateRange)                  END            FROM cteRange            WHERE DateRange <=                   CASE                        WHEN @Increment = ‘d‘ THEN DATEADD(dd, -1, @EndDate)                        WHEN @Increment = ‘w‘ THEN DATEADD(ww, -1, @EndDate)                        WHEN @Increment = ‘m‘ THEN DATEADD(mm, -1, @EndDate)                  END        )        INSERT INTO @SelectedRange(iDate,iYear,iMonth,iYM)        SELECT DateRange, YEAR(DateRange) [Year], MONTH(DateRange) [Month] ,CONVERT(VARCHAR(7),DateRange,102)[YM]        FROM cteRange        OPTION (MAXRECURSION 0);        RETURNEND

SELECT * FROM DateRange()

SELECT * FROM DateRange(default,default,default)

SELECT * FROM DateRange(‘d‘,‘2014/1/1‘,‘2014/12/1‘)