How to display all the dates between two given dates in SQL

Question

Using SQL server 2000. If the Start date is 06/23/2008 and End date is 06/30/2008

Then I need the Output of query as

06/23/2008
06/24/2008
06/25/2008
.
.
.
06/30/2008

I Created a Table names as Integer which has 1 Column, column values are 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 then I used the below mentioned query

Tried Query

SELECT DATEADD(d, H.i * 100 + T .i * 10 + U.i, '" & dtpfrom.Value & "') AS Dates 
  FROM integers H 
CROSS JOIN integers T 
CROSS JOIN integers U 
order by dates

The above query is displaying 999 Dates only. 999 Dates means (365 + 365 + 269) Dates Only. Suppose I want to select more than 3 Years (01/01/2003 to 01/01/2008). The above query should not suitable.

How to modify my query? Or any other query is available for the above condition.

Please kindly provide me the Query.

Solution 1

This will get you up to 100,000 days:

SELECT DATEADD(d, Y.i * 10000 + X.i * 1000 + H.i * 100 + T .i * 10 + U.i, '" & dtpfrom.Value & "') AS Dates 
FROM integers H 
CROSS JOIN integers T 
CROSS JOIN integers U 
CROSS JOIN integers X 
CROSS JOIN integers Y 
order by dates

Solution 2

I wouldn't loop to create a list of dates, use a Numbers table (not just a table of values 0 to 9), they are usefull for many things: http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html With a true Numbers table you don't have to CROSS JOIN a bunch of times and make the query overly complex.

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.columns s1
    CROSS JOIN sys.columns s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, use this query:

SELECT
    @Start+Number-1
    FROM Numbers
    WHERE Number<=DATEDIFF(day,@Start,@End)+1

to capture them do:

DECLARE  @Start datetime
         ,@End  datetime
DECLARE @AllDates table
        (Date datetime)

SELECT @Start = '06/23/2008', @End = '06/30/2008'

INSERT INTO @AllDates
        (Date)
    SELECT
        @Start+Number-1
        FROM Numbers
        WHERE Number<=DATEDIFF(day,@Start,@End)+1

SELECT * FROM @AllDates

output:

Date
-----------------------
2008-06-23 00:00:00.000
2008-06-24 00:00:00.000
2008-06-25 00:00:00.000
2008-06-26 00:00:00.000
2008-06-27 00:00:00.000
2008-06-28 00:00:00.000
2008-06-29 00:00:00.000
2008-06-30 00:00:00.000

(8 row(s) affected)

Solution 3

One possible way (not saying it's the best or most efficient) would be something like this:

DECLARE @StartDate DATETIME
SET @StartDate = '06/23/2008'

DECLARE @EndDate DATETIME 
SET @EndDate = '06/30/2008'

DECLARE @TableOfDates TABLE(DateValue DATETIME)

DECLARE @CurrentDate DATETIME

SET @CurrentDate = @startDate

WHILE @CurrentDate <= @endDate
BEGIN
    INSERT INTO @TableOfDates(DateValue) VALUES (@CurrentDate)

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

SELECT * FROM @TableOfDates

This will work with any number of dates, any range of dates, and doesn't need a specific "helper" table with integer values.

It stores all relevant dates into a in-memory table variable so you can then use it for e.g. another SELECT statement or whatever you need it for.

Marc

Solution 4

See:

Why should I consider using an auxiliary calendar table?

A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:

• How many business days between x and y?
• ...

View more solutions