Tuesday, May 23, 2017

All Dates or Number series between start and end position - SQL SERVER Months between two dates, Numbers series between to number


Example 1: Date Series
DECLARE @StartDate  DATETIME ='20110501' ,
        @EndDate    DATETIME= '20110801'

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'      
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

Example 1: Int Series
DECLARE @StartRange  int ='2' ,
        @EndRange     int= '10'

SELECT  x.number AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'      
AND     x.number between @StartRange and  @EndRange