日期范围内的日期 [英] Dates in a date range
问题描述
有没有办法可以在给定日期范围内获得包含唯一日期的结果集，而不需要临时表和
游标？
或许类似于：
将@start_date声明为datetime
将@end_date声明为datetime
将@start_date设为''1/1/2005''
将@end_date设为''1/1/2006''
select fn_getuniquedate（@start_date ，@ end_date）
1/1/2005
1/2/2005
1/3/2005
...
12/31/2005
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)
1/1/2005
1/2/2005
1/3/2005
...
12/31/2005
推荐答案
任何理由你都不能在数据库中创建一个永久的日历表？
日历对于许多类型的查询都很有用，所以如果你需要对日期做任何事情，那么有一个
是有意义的。 br />
SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @start_date AND @end_date;
否则，你合作uld编写一个迭代的表值函数来生成数据的
。在大多数情况下，不太可能比永久桌子表现更好
虽然。
-
David Portas
SQL Server MVP
-
" PromisedOyster" <镨************ @ hotmail.com>在消息中写道
news：11 ********************** @ g49g2000cwa.googlegr oups.com ...
Any reason why you can''t create a permanent Calendar table in your database?
Calendars are useful for many types of query so it makes sense to have one
if you need to do anything with dates.
SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @start_date AND @end_date ;
Otherwise, you could write an iterative table-valued function to generate
the data. Unlikely to perform better than a permanent table in most cases
though.
--
David Portas
SQL Server MVP
--
"PromisedOyster" <Pr************@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
有没有办法可以在给定日期范围内获得包含唯一日期的结果集，而无需使用临时表和
游标？
或许类似于：
将@start_date声明为datetime
将@end_date声明为datetime
将@start_date设置为''1/1/2005''
set @ end_date为''1/1/2006''
选择fn_getuniquedate（@start_date，@ end_date）
1/1/2005
1/2/2005
2005年1月3日
2005年12月31日
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)
1/1/2005
1/2/2005
1/3/2005
..
12/31/2005
获取SQL FOR SMARTIES的副本并查找用于日历
表。你需要停止思考函数并开始思考
表格和连接的条款。
Get a copy of SQL FOR SMARTIES and look up the uses for a Calendar
table. You need to stop thinking about functions and start thinking in
terms of tables and joins.
PromisedOyster（Pr *** *********@hotmail.com）写道：
PromisedOyster (Pr************@hotmail.com) writes:
有没有办法让我得到一个结果集，其中包含
给定日期范围内的唯一日期而无需有一个临时表和一个
游标？
可能是这样的：
声明@start_date为datetime
声明@end_date为datetime 将@start_date设置为''1/1/2005''
将@end_date设置为''1/1/2006''
选择fn_getuniquedate（@start_date，@ end_date）
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)
正如David和Celko所说，最好一次将它存放在一张桌子上。
他们没有说的是如何填充它。这是我如何填写我们的日期
表，日期从1990年到2150年。根据需要调整：
TRUNCATE TABLE日期
go
- 获得数字的诱惑力。这是一个便宜的，但不是100％可靠。
- 从而查询提示和所有检查。
SELECT TOP 80001 n = IDENTITY（int，0,1 ）
INTO #numbers
来自sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN系统对象o4
选项（MAXDOP 1）
go
- 确保我们有唯一的数字。 />
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers（n）
go
- 确认表没有间隙。
IF（SELECT COUNT（*）FROM #numbers）= 80001 AND
（SELECT MIN（n）FROM #numbers）= 0 AND
（SELECT MAX（n ）FROM FROM #numbers）= 80000
BEGIN
DECLARE @msg varchar（255）
- 插入日期：
INSERT日期（thedate）
SELECT dateadd（DAY，n，'''19800101''）
FROM #numbers
WHERE dateadd（DAY，n，'''19800101''）< ''21500101''
SELECT @msg =''插入''+ ltrim（str（@@ rowcount））+
''行到＃数字''
PRINT @msg
结束
ELSE
RAISERROR（''＃number不是来自0到80001！''，16，-1）
go
DROP TABLE #numbers
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
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, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于日期范围内的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
