pulling all dates within a date range

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?

thanks in advanceOn 6 Jul 2006 14:14:40 -0700, rugger81 wrote:

Quote:

Originally Posted by

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

(snip)

Hi rugger81,

http://www.aspfaq.com/show.asp?id=2519
--
Hugo Kornelis, SQL Server MVP|||rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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:

Quote:

Originally Posted by

rugger81 (jgilchrist@.ots.net) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx