I need to dynamically generate a list of days in a given date range

Question

Post reply

I need to dynamically generate a list of days in a given date range

Andrew Stanton

Ten Centuries

Points: 1115
More actions
September 18, 2007 at 5:38 am

#374303
I need to be able to generate a list of days in a given date range, and shove them into a table variable or temp table. For example, if I give '01/01/2007 00:00:00' as the start and '01/05/2007 00:00:00' as the end, the output (table contents) should look like this
```
dates
```
```
------------
```
```
01/01/2007
```
```
01/02/2007
```
```
01/03/2007
```
```
01/04/2007
```
```
01/05/2007
```
Here is the catch, I want to be able to do this without using while/cursor or looping operations, and there is no preexisting "dates" table to use. I am using SQL 2000 so CTE is out. Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

> and there is no preexisting "dates" table to use.

Then create it.

_____________
Code for TallyGenerator

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 2

...thats kind of the point, I need to create it without using a loop or cursor. I have made it work with a while loop, but its not preferable to do so.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

You probably misunderstood what Sergiy meant... you should create permanent Numbers (or Dates) table, once and for ever. Then you can use it in such queries as you described.

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 4

no, i get it. that was the first suggestion of a colleague, but I still need to know if there is a way to populate a numbers table using set based logic.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Vladan

SSC-Insane

Points: 22072

September 18, 2007 at 6:00 am

#734271

Look for the post (reply) by Jeff Moden here

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 6

Once again, Jeff proves to be awesome. (He is scary with some of this stuff)

Thanks for finding that post Vladan.