How To Get A Table Of Dates Between X And Y In Sql Server 2005

September 08, 2024 Post a Comment

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

Solution 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 (
        SELECT1UNIONALLSELECT1+ n FROM numbers WHERE n <500 )
    SELECT DATEADD(day,n-1,'2008/11/01') FROM numbers
    OPTION ( MAXRECURSION 500 )

Solution 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
        leftouterjoin
        ...
where   Calendar.Date >=@xand     Calendar.Date <=@y

Solution 3:

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.

Solution 4:

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.

Solution 5:

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@tmpDatestable   
            (StatsDate datetime)
Set@FromDate= DateAdd(day,-30,GetDate())
Set@ToDate= GetDate()

InsertInto@tmpDates (StatsDate)
SelectdistinctCAST(FLOOR(CAST(visitDate ASDECIMAL(12, 5))) AS DATETIME)
FROM tbl_visitorstats 
Where visitDate between@FromDateAnd@ToDateOrderByCAST(FLOOR(CAST(visitDate ASDECIMAL(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.

Lab Sql