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

Nath

I believe you're looking for this blog post. – Toughminded

It's dirty but it does the job – Nath

And it's portable to other SQL engines (including Server 2000). :) – Thermosetting

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 )

Fibril

The Option clause does not accept a variable as input, so one must know how many days at the time of writing the code. Otherwise: fun stuff. – Cassey

But the Where clause does, the MAXRECURSION is just a fail over if the loop goes on to far. – Nath

If you can't use CTEs (like in SQL Server 2000), then you can use what I wrote here musingmarc.blogspot.com/2006/07/… – Thermosetting

@Fibril I can't understand what you're doing here (I'm no SQL Pro). Can you provide a brief explanation? Does this only work in SQL Server or can it be used in a Postgres database? – Indebtedness

Answer 2

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

Colorable

Answer 3

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

Inventive

Answer 4

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.

Crowning

Answer 5

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.

Olympia

Answer 6

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.

Nath

Answer 7

A slight twist on the answer given as https://mcmap.net/q/825165/-how-to-get-a-table-of-dates-between-x-and-y-in-sql-server-2005. 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 )

Dagley

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

Cassey

Answer 9

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

Smoko

He's trying to create a table to do a left outer join on. This will not do what he wants to do. – Crowning