Post reply

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

  • 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?

  • September 18, 2007 at 5:49 am

    #734264

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

    Then create it.

    _____________
    Code for TallyGenerator

  • September 18, 2007 at 5:52 am

    #734265

    ...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.

  • September 18, 2007 at 5:54 am

    #734266

    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.

  • September 18, 2007 at 5:57 am

    #734269

    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.

     

  • September 18, 2007 at 6:00 am

    #734271

    Look for the post (reply) by Jeff Moden here

  • September 18, 2007 at 7:05 am

    #734287

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

    Thanks for finding that post Vladan.

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

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