How to display all dates between two dates in SQL

Question

Using SQL Server 2000. If the start date is 06/23/2008 and the end date is 06/23/2008

Then I need the query output as

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

I created the table names as Integer, which has 1 column, the column values ​​are 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, then I used the query below

Test request

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

In the above query, only 999 dates are displayed. 999 Dates mean (365 + 365 + 269) Dates only. Suppose I want to choose more than 3 years (from 01/01/2003 to 01/01/2008). The above query is not suitable.

How to change my request? Or any other request is available for the above condition.

Please provide me a request.

Answer 1

This will allow you up to 100,000 days:

 SELECT DATEADD(d, Yi * 10000 + Xi * 1000 + Hi * 100 + T .i * 10 + Ui, '" & 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 

Answer 2

I would not loop to create a list of dates, use the Numbers table (and not just the table of values ​​from 0 to 9), they are useful for many things: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider -using-an-auxiliary-numbers-table.html With the true numbers table, you do not need CROSS JOIN several times and make the query too complicated.

For this method to work, you need to complete this setup at once:

 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 configured, use this query:

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

to fix them:

 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) 

Answer 3

One of the possible ways (not to say that it is the best or most effective) 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 date range and does not need a specific helper table with integer values.

It stores all the relevant dates in a table variable in memory, so you can use it for, for example, another SELECT statement or whatever you need.

Mark

Answer 4

Cm:

Why should I use an auxiliary calendar table?

A calendar table can make a lot easier to develop solutions around any business model that includes dates. The last thing I checked, it covers pretty much any business model that you might think to some extent. persistent problems that ultimately require verbose, complex, and ineffective methods include the following questions:

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