declare @StartDate date = '20120101', @EndDate date = '20121230', @Interval int = 2;with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)), -- 10 rows e2(n) as (select 1 from e1 a cross join e1 b), -- 100 rows e4(n) as (select 1 from e2 a cross join e2 b), -- 10,000 rows eTally(n) as (select row_number() over (order by (select null)) from e4 a cross join e2 b) -- 1,000,000 rowsselect top ((datediff(dd, @StartDate, @EndDate) + @Interval + 1)/(@Interval + 1)) dateadd(dd, (1 + @Interval) * (n - 1), @StartDate)from eTally
DECLARE @Fromdate DATE = '01 Jan 2012' ,@Todate DATE = '30 Dec 2012' ,@Interval INT = 2SELECT DateC , NFROM ( SELECT DATEADD(DAY, N, @Fromdate ) AS DateC, N FROM (SELECT (ROW_NUMBER() OVER (ORDER BY [object_id]) - 1) * (@Interval + 1) FROM sys.columns) dt(N) ) RWHERE DateC <= @Todate