Query to return +- 30 days from a specific date

Question

I'm trying to figure out how to write a query that will return a table of 61 record that will list a date for each record from the current date.

Answer 1

This is a useful function I use, taken from here:

Explode Dates Between Dates, check and adjust parameter

Just send it Date-30 and Date+30

CREATE FUNCTION [dbo].[ExplodeDates] (@startdate DATETIME, @enddate DATETIME)
RETURNS TABLE 
AS 
    RETURN (
        WITH 
         N0 AS (SELECT 1 AS n UNION ALL SELECT 1)
        ,N1 AS (SELECT 1 AS n FROM N0 t1, N0 t2)
        ,N2 AS (SELECT 1 AS n FROM N1 t1, N1 t2)
        ,N3 AS (SELECT 1 AS n FROM N2 t1, N2 t2)
        ,N4 AS (SELECT 1 AS n FROM N3 t1, N3 t2)
        ,N5 AS (SELECT 1 AS n FROM N4 t1, N4 t2)
        ,N6 AS (SELECT 1 AS n FROM N5 t1, N5 t2)
        ,nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS num FROM N6)
        SELECT DATEADD(day, num-1, @startdate) AS thedate
        FROM nums
        WHERE num <= DATEDIFF(day, @startdate, @enddate) + 1
    );
GO

If you don't want the function, you can also simply use it as a query, declaring

@startdate = @myDate - 30 and
@enddate = @myDate + 30

Answer 2

The simplest, and probably most efficient way in SQL-Server to get a list of 61 dates is to use the system table Master.dbo.spt_values:

SELECT  [Date] = DATEADD(DAY, number - 30, CAST(CURRENT_TIMESTAMP AS DATE))
FROM    Master..spt_values
WHERE   Type = 'P'
AND     Number <= 60;

Example on SQL Fiddle

---

EDIT

If you are concerned about using undocumented system tables then this will do the same thing (again with no looping)

WITH T AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY Object_ID)
    FROM    sys.all_objects
)
SELECT  [Date] = DATEADD(DAY, number - 30, CAST(CURRENT_TIMESTAMP AS DATE))
FROM    T
WHERE   Number <= 60;

Example on SQL Fiddle

Extensive testing has been done here on the merits of various methods of generating sequences of numbers. My preferred option would always be your own table (e.g. dbo.numbers, or in this case a calendar table).

Answer 3

Try this

;with DateList As
(
    select GETDATE() as DateCol
    union all
    select datecol + 1 from datelist
    where DateDiff(d, getdate(),datecol+1) < 31 and DateCol + 1 > GETDATE()
    union all
    select datecol - 1 from datelist
    where DateDiff(d, datecol-1, getdate())  < 31 and DateCol - 1 < GETDATE()
)
select CONVERT(varchar(15), DateCol, 101) DateCol from DateList
order by 1 
OPTION (MAXRECURSION 0)

If you want to join other table

declare @t table (code varchar(10));
insert into @t 
values ('a'), ('b')

;with DateList As
(
    select GETDATE() as DateCol
    union all
    select datecol + 1 from datelist
    where DateDiff(d, getdate(),datecol+1) < 31 and DateCol + 1 > GETDATE()
    union all
    select datecol - 1 from datelist
    where DateDiff(d, datecol-1, getdate())  < 31 and DateCol - 1 < GETDATE()
)
select * from DateList, @t
OPTION (MAXRECURSION 0)

Answer 4

In my opinion, the best way to approach this is not to use recursive ctes, temp tables, or system tables, but rather to create and reuse a date lookup table. Create the lookup table once, and then you can use it as needed.

From there, it's really easy to generate a list of dates:

select * 
from datelookup
where datefull >= dateadd(day,-30,convert(varchar(10), getDate(), 120)) 
  and datefull <= dateadd(day,30,convert(varchar(10), getDate(), 120));

SQL Fiddle Demo (includes sample code to create such a table)

Answer 5

This T-SQL code will generate your table:

DECLARE @dates TABLE (date_item DATE) 
DECLARE @day DATE = DATEADD(DAY, -30, N'2013-05-02')
WHILE @day <= DATEADD(DAY, 30, N'2013-05-02') 
BEGIN
   INSERT INTO @dates (date_item) SELECT @day
   SET @day = DATEADD(DAY, 1, @day)
END

The result is in @dates. Obviously you will need to set the desired value for the center date in place of N'2013-05-02'