How to get a table of dates between x and y in sql server 2005

Question

I just want a quick way (and preferably not using a while loop)of createing a table of every date between date @x and date @y so I can left outer join to some stats tables, some of which will have no records for certain days in between, allowing me to mark missing days with a 0

Answer 1

Strictly speaking this doesn't exactly answer your question, but its pretty neat.

Assuming you can live with specifying the number of days after the start date, then using a Common Table Expression gives you:

WITH numbers ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM numbers WHERE n < 500 )
    SELECT DATEADD(day,n-1,'2008/11/01') FROM numbers
    OPTION ( MAXRECURSION 500 )

Answer 2

You'll have to edit the LEFT JOIN statement below so that it labels your stats tables to fit your usecase. In the meantime, here's something inspired by BigJump's answer, written in TSQL.

• Objective: Return all gap days in the dataset idl_sourceTable, where a gap day is a day for which there are no corresponding records in idl_sourceTable.
• Constraints: No loops

Requirements:

1. A table must be created which contains contiguous dates.
2. A startDate and endDate must be specifiable as input.
3. The result should allow detection of missing days from other tables whose records have a DATETIME field.

-- Declare parameters based on [timeGenerated] of idl_sourceTable

    DECLARE @startDate DATE SET @startDate = ( SELECT CAST (MIN ([timeGenerated]) AS DATE) FROM idl_sourceTable )
    DECLARE @endDate DATE SET @endDate = ( SELECT CAST (MAX ([timeGenerated]) AS DATE) FROM idl_sourceTable )
    DECLARE @dateRange INT SET @dateRange = ( SELECT DATEDIFF (DAY, @startDate, @endDate) )

    SELECT @startDate, @endDate, @dateRange;
    
-- Create #tempDateTable containing dates delimited between the MIN and MAX timeGenerated of idl_sourceTable

    DROP TABLE IF EXISTS #tempDateTable;
            
    WITH numbers_CTE ( n ) AS (
            SELECT 1 UNION ALL
            SELECT 1 + n FROM numbers_CTE WHERE n <= @dateRange )
        SELECT DATEADD (DAY, n-1, @startDate) AS [date] INTO #tempDateTable FROM numbers_CTE
        OPTION ( MAXRECURSION 0 ) -- disables the default 100 recursion level for the CTE

    SELECT * FROM #tempDateTable

-- Display dates which are not represented in idl_sourceTable

    SELECT basis.[date] AS [missingDays] FROM #tempDateTable basis
    LEFT JOIN ( SELECT DISTINCT CAST ( [timeGenerated] AS DATE ) AS [objectDate] FROM idl_sourceTable ) AS object ON object.[objectDate] = basis.[date]
    WHERE object.[objectDate] IS NULL

Answer 3

I would create a Calendar table that just contained every date from a suitable start date until a suitable end date. This wouldn't take up much space in your database and would make these types of query child's play.

select  ...
from    Calendar
        left outer join
        ...
where   Calendar.Date >= @x
and     Calendar.Date <= @y

Answer 4

A slight twist on the answer given as https://stackoverflow.com/a/95728/395440. Allows days to be specified and also calculates range up to the current date.

DECLARE @startDate datetime
SET @startDate = '2015/5/29';

WITH number ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM dates WHERE n < DATEDIFF(Day, @startDate, GETDATE()) )
    SELECT DATEADD(day,n-1,@startDate) FROM number where
    datename(dw, DATEADD(day,n-1,@startDate)) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')
    OPTION ( MAXRECURSION 500 )

Answer 5

I think that you might as well just do it in a while loop. I know it's ugly, but it's easy and it works.

Answer 6

I was actually doing something similar a little while back, but I couldn't come up with a way that didn't use a loop.

The best I got was a temp table, and then selecting the dates I wanted to join on into that.

The blog bduke linked to is cute, although I think the temp table solution is perhaps a cleaner solution.

Answer 7

I've found another table that stores every date (it's visitors to the website), so how about this...

Declare @FromDate datetime,  
        @ToDate datetime  
Declare @tmpDates table   
            (StatsDate datetime)
Set @FromDate = DateAdd(day,-30,GetDate())
Set @ToDate = GetDate()

Insert Into  @tmpDates (StatsDate)
Select 
    distinct CAST(FLOOR(CAST(visitDate AS DECIMAL(12, 5))) AS DATETIME)
FROM tbl_visitorstats 
Where visitDate between @FromDate And @ToDate 
Order By CAST(FLOOR(CAST(visitDate AS DECIMAL(12, 5))) AS DATETIME) 


Select * FROM @tmpDates

It does rely on the other table having an entry for every date I want, but it's 98% likely there'll be data for every day.

Answer 8

Just write the loop. Someone has to write a loop for this, be it you - or SQL Server.

DECLARE @Dates TABLE
(
  TheDate datetime PRIMARY KEY
)
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '2000-01-01', @EndDate = '2010-01-01'


DECLARE @LoopVar int, @LoopEnd int    
SELECT @LoopEnd = DateDiff(dd, @StartDate, @EndDate), @LoopVar = 0


WHILE @LoopVar <= @LoopEnd
BEGIN
  INSERT INTO @Dates (TheDate)
  SELECT DateAdd(dd,@LoopVar,@StartDate)

  SET @LoopVar = @LoopVar + 1
END


SELECT *
FROM @Dates

Answer 9

Just: WHERE col > start-date AND col < end-date