|
|
Posted by Erland Sommarskog on 10/10/05 00:34
PromisedOyster (PromisedOyster@hotmail.com) writes:
> Is there a way that I can get a resultset that contains unique dates in
> a given date range without the need to have a temporary table and a
> cursor?
>
> perhaps something like:
>
> declare @start_date as datetime
> declare @end_date as datetime
> set @start_date as '1/1/2005'
> set @end_date as '1/1/2006'
> select fn_getuniquedate(@start_date, @end_date)
As David and Celko said, better store this in a table once for all.
What they didn't say was how to fill it. Here is how I fill our dates
table with dates from 1990 to 2150. Adapt as you like:
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 SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|