How to declare dates in SQL multiple value

Question

I am trying to create my SQL syntax so that we can have versatile input. I figured out how to do it for one set date and it worked. my syn is:

DECLARE @MyDay AS VARCHAR(50);
DECLARE @NextDay AS VARCHAR(50);

SET @MyDay = '8/30/2016';
SET @NextDay = DATEADD(d, 1, @MyDay);

However, I'm stuck with how to do it for multiple dates. Ideally I would like to put in a range of date, and from that it will scan the records, i.e. set range between oct 1st and oct 5th.

I'm using SQL Server Management Studio 2008

Answer 1

If you just want a series of days and next days between 8/30/16 and 9/5/16, you can use a derive table method like below.

declare @n int;
declare @StartDate datetime, @EndDate datetime;

set @n = 5;
set @StartDate = '20160830';
set @EndDate = dateadd(day, @n, @StartDate);

select cast(dateadd(day, number, @StartDate) as date) as MyDate,
 cast(dateadd(day, number + 1, @StartDate) as date) as MyNextDate
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @StartDate) < @EndDate;

Alternately you can use a temp table, table variable to store your dates, or a cte as well.

A recursive cte imlementation example is given below. Please note you would want to set MAXRECURSION option if you have a long date range as default for max recursion is 100.

declare @n int;
declare @StartDate datetime, @EndDate datetime;

set @n = 5;
set @StartDate = '20160830';
set @EndDate = dateadd(day, @n, @StartDate);

 ;with DateSeq as
 (
 select cast(@StartDate as date) as MyDate
 union all
 select dateadd(day , 1, MyDate) AS MyDate
    from DateSeq where dateadd (day, 1, MyDate) < @EndDate
 )
select MyDate, dateadd(day , 1, MyDate) AS NextDate
 from DateSeq;

A temp table implementation example is as below.

declare @MyDateRange table(MyDate date);

 Insert into @MyDateRange values('8/30/2016')
 Insert into @MyDateRange values('9/1/2016')
 Insert into @MyDateRange values('9/2/2016')
 Insert into @MyDateRange values('9/3/2016')
 Insert into @MyDateRange values('9/4/2016')

select MyDate, dateadd(day, 1, MyDate) as NextDate from @MyDateRange