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.
This is a useful function I use, taken from here:
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;
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;
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).