SQL generates a date range

Sometimes want to generate a sequence by day or month, just like 2014-1-1, 2014-1-2, 2014-1-3 ...

In SQL Server, you can write a function to implement.

/*generate a date range, such as 2014.01, 2014.02 ... @Increment step value @startdate Start date @enddate End Date----------------------@SelectedRange return value name (the return value of a multi-statement table-valued function must be stored in the return value variable) returns the field and type of the table: IDate DATETIME, Iyear char (4), Imonth char (2), IYm char (7)*/CREATE FUNCTION [dbo].[DateRange](    @Increment Char(1)= 'm',    @StartDateDATE='20140101',    @EndDateDATE=NULL)RETURNS @SelectedRange TABLE(iDate DATE, IyearChar(4), ImonthChar(2), IYmCHAR(7)) asBEGIN/*The Default value for stored procedures parameter has to be constants. The function GETDATE () returns a different value from time-to-time, so it's not a constant expression.*/--IF @EndDate is null--SET @EndDate = getdate ()--orSET @EndDate=COALESCE(@EndDate,GetDate())        ; withCterange (DateRange) as        (            SELECT CAST(@StartDate  asDATE)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             fromCterangeWHEREDateRange<=                    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)SELECTDateRange, Year(DateRange)[ Year],MONTH(DateRange)[Month],CONVERT(VARCHAR(7), DateRange,102)[YM]         fromCterangeOPTION(maxrecursion0); RETURNEND

The 3 parameters of this function all set the default value, then how to use when the call?

SELECT *  from DateRange ()

This does not work, if you want to use the default value, then write default:

SELECT *  from DateRange (default,default,default)

If there are parameters to pass in:

SELECT *  from DateRange ('d','2014/1/1','2014 /12/1')

This will show data for the day from January 1 to December 1:

Note that it is not possible to write the GETDATE () function in the parameters of the function, because the parameter is to be constant, and the GETDATE function is a variable, so make a modification.

--end--

SQL generates a date range