Query to return +- 30 days from a specific date

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.

Answers


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


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).


Need Your Help

Is it possible to host a Sencha-powered mobile website on Amazon S3?

amazon-s3 sencha-touch sencha-touch-2

Amazon S3 static website hosting sounds really tempting - simple to use, reliable, and the price is right.

How do I know why stat failed?

c file struct

I want to know if file is exists with C.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.