Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    2

    Unanswered:

    I like nigelrivett's answer. My contribution is less efficient, but doesn't have a defined upper limit on the datediff.

    It uses the table variable, so won't work in SQL 6.5 or 7.0 (you'll have to use a temporary table there - which has more drawbacks).


    declare @startdate datetime, @enddate datetime
    select @startdate = '1 feb 2001', @enddate = '20 sep 2010'

    declare @max_number int
    set @max_number = datediff(d, @startdate, @enddate)

    --create a table of numbers from 0 to @max_number
    set nocount on
    declare @t table (i int)
    insert into @t values (0)

    declare @i int
    set @i = 1
    while @i < @max_number/2
    begin
    insert into @t select i + @i from @t
    set @i = @i * 2
    end
    insert into @t select i + @i from @t where i + @i <= @max_number
    set nocount off

    --use the table
    select d = dateadd(dd, i, @startdate) from @t order by d

  2. #2
    Join Date
    Feb 2002
    Posts
    2
    Just for overkill, I've created a user-defined function which takes a number and returns a table of numbers from 0 to that number. That can then be used to solve this problem and elsewhere.

    This will only work in SQL 2000 (as far as I know)...

    CREATE FUNCTION table_of_numbers (@max_number int)
    RETURNS @t table (i int)
    AS
    begin
    insert into @t values (0)

    declare @i int
    set @i = 1
    while @i < @max_number/2
    begin
    insert into @t select i + @i from @t
    set @i = @i * 2
    end
    insert into @t select i + @i from @t where i + @i <= @max_number

    RETURN
    end

    So...

    SELECT * FROM table_of_numbers(10)

    Gives...

    i
    -----------
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10


    And for this specific problem...

    declare @startdate datetime, @enddate datetime
    select @startdate = '1 feb 2001', @enddate = '20 sep 2001'

    declare @max_number int
    set @max_number = datediff(d, @startdate, @enddate)
    select d = dateadd(dd, i, @startdate) from table_of_numbers(@max_number) order by d

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •