Posted by rugger81 on 07/07/06 13:28

Thanks guys, I'll do just that. The idea of creating a date table
crossed my mind before, but I like to do things dynamically. Now that
I think of it however, a date table for the any time frame I would need
would still be relatively small and would save alot of time.

Erland Sommarskog wrote:
> rugger81 (jgilchrist@ots.net) writes:
> > I am currently working in the sql server 2000 environment and I want to
> > write a function to pull all dates within a given date range. I have
> > created several diferent ways to do this but I am unsatisfied with
> > them. Here is what I have so far:
> >
> > declare @Sdate as datetime
> > declare @Edate as datetime
> >
> > set @SDate = '07/01/2006'
> > set @EDate = '12/31/2006'
> >
> > select dateadd(dd, count(*) - 1, @SDate)
> > from [atable] v
> > inner join [same table] v2 on v.id < v2.id
> > group by v.id
> > having count(*) < datediff(dd, @SDate, @EDate)+ 2
> > order by count(*)
> >
> > this works just fine but it is dependent on the size of the table you
> > pull from, and is really more or less a hack job. Can anyone help me
> > with this?
>
> If I understand this correctly, given the sample data you want
>
> 2006-01-07, 2006-01-08, ... 2006-12-30, 2006-12-31
>
> The best is simply to create a table of dates. Here is a script that
> create our dates table:
>
>
>
> TRUNCATE TABLE dates
> go
> -- Get a temptable with numbers. This is a cheap, but not 100% reliable.
> -- Whence the query hint and all the checks.
> SELECT TOP 80001 n = IDENTITY(int, 0, 1)
> INTO #numbers
> FROM sysobjects o1
> CROSS JOIN sysobjects o2
> CROSS JOIN sysobjects o3
> CROSS JOIN sysobjects o4
> OPTION (MAXDOP 1)
> go
> -- Make sure we have unique numbers.
> CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
> go
> -- Verify that table does not have gaps.
> IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
> (SELECT MIN(n) FROM #numbers) = 0 AND
> (SELECT MAX(n) FROM #numbers) = 80000
> BEGIN
> DECLARE @msg varchar(255)
>
> -- Insert the dates:
> INSERT dates (thedate)
> SELECT dateadd(DAY, n, '19800101')
> FROM #numbers
> WHERE dateadd(DAY, n, '19800101') < '21500101'
>
> SELECT @msg = 'Inserted ' + ltrim(str(@@rowcount)) + ' rows into
> #numbers'
> PRINT @msg
> END
> ELSE
> RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
> go
> DROP TABLE #numbers
>
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]