如何在 SQL Server 2000 中生成填充日期的临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6125504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I generate a temporary table filled with dates in SQL Server 2000?
提问by Sailing Judo
I need to make a temporary table that holds of range of dates, as well as a couple of columns that hold placeholder values (0) for future use. The dates I need are the first day of each month between $startDate and $endDate where these variables can be several years apart.
我需要制作一个包含日期范围的临时表，以及一些包含占位符值 (0) 以备将来使用的列。我需要的日期是 $startDate 和 $endDate 之间每个月的第一天，其中这些变量可以相隔几年。
My original sql statement looked like this:
我原来的 sql 语句是这样的：
select dbo.FirstOfMonth(InsertDate) as Month, 0 as Trials, 0 as Sales
into #dates
from customer
group by dbo.FirstOfMonth(InsertDate)
"FirstOfMonth" is a user-defined function I made that pretty much does what it says, returning the first day of the month for the provided date with the time at exactly midnight.
“FirstOfMonth”是我创建的一个用户定义函数，它几乎按照它所说的去做，返回所提供日期的月份第一天，时间正好是午夜。
This produced almost exactly what I needed until I discovered there were occasionally gaps in my dates where I had a few months were there were no records insert dates. Since my result must still have the missing months I need a different approach.
这几乎产生了我需要的东西，直到我发现我的日期偶尔会出现间隔，因为我有几个月没有记录插入日期。由于我的结果必须仍然有缺失的月份，我需要一种不同的方法。
I have added the following declarations to the stored procedure anticipating their need for the range of the dates I need ...
我已将以下声明添加到存储过程中，预计他们需要我需要的日期范围......
declare $startDate set $startDate = select min(InsertDate) from customer
declare $endDate set $endDate = select max(InsertDate) from customer
... but I have no idea what to do from here.
......但我不知道从这里开始做什么。
I know this question is similar to this questionbut, quite frankly, that answer is over my head (I don't often work with SQL and when I do it tends to be on older versions of SQL Server) and there are a few minor differences that are throwing me off.
我知道这个问题与这个问题相似，但坦率地说，这个答案超出了我的头脑（我不经常使用 SQL，当我这样做时，它往往是在旧版本的 SQL Server 上），并且有一些次要的使我失望的差异。
回答by ClubbedAce
I needed something similar, but all DAYS instead of all MONTHS.
我需要类似的东西，但所有的日子而不是所有的月份。
Using the code from MatBailie as a starting point, here's the SQL for creating a permanent table with all dates from 2000-01-01 to 2099-12-31:
使用 MatBailie 中的代码作为起点，这里是用于创建所有日期从 2000-01-01 到 2099-12-31 的永久表的 SQL：
CREATE TABLE _Dates (
d DATE,
PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'
DECLARE @dEnd DATE = '2100-01-01'
WHILE ( @dIncr < @dEnd )
BEGIN
INSERT INTO _Dates (d) VALUES( @dIncr )
SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END
回答by MatBailie
This will quickly populate a table with 170 years worth of dates.
这将很快用 170 年的日期填充表。
CREATE TABLE CalendarMonths (
date DATETIME,
PRIMARY KEY (date)
)
DECLARE
@basedate DATETIME,
@offset INT
SELECT
@basedate = '01 Jan 2000',
@offset = 1
WHILE (@offset < 2048)
BEGIN
INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
SELECT @offset = @offset + @offset
END
You can then use it by LEFT joining on to that table, for the range of dates you require.
然后，您可以通过 LEFT 连接到该表来使用它，用于您需要的日期范围。
回答by Tom H
I would probably use a Calendar table. Create a permanent table in your database and fill it with all of the dates. Even if you covered a 100 year range, the table would still only have ~36,525 rows in it.
我可能会使用日历表。在您的数据库中创建一个永久表并用所有日期填充它。即使您涵盖了 100 年的范围，该表中仍然只有约 36,525 行。
CREATE TABLE dbo.Calendar (
calendar_date DATETIME NOT NULL,
is_weekend BIT NOT NULL,
is_holiday BIT NOT NULL,
CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)
Once the table is created, just populate it once in a loop, so that it's always out there and available to you.
创建表后，只需在循环中填充它一次，以便它始终存在并可供您使用。
Your query then could be something like this:
您的查询可能是这样的：
SELECT
C.calendar_date,
0 AS trials,
0 AS sales
FROM
dbo.Calendar C
WHERE
C.calendar_date BETWEEN @start_date AND @end_date AND
DAY(C.calendar_date) = 1
You can join in the Customers table however you need to, outer joining on FirstOfMonth(InsertDate) = C.calendar_dateif that's what you want.
您可以根据需要加入客户表，FirstOfMonth(InsertDate) = C.calendar_date如果这是您想要的，则可以外部加入。
You can also include a column for day_of_month if you want which would avoid the overhead of calling the DAY()function, but that's fairly trivial, so it probably doesn't matter one way or another.
如果您愿意，您还可以为 day_of_month 包含一列，这样可以避免调用DAY()函数的开销，但这是相当微不足道的，因此无论哪种方式都可能无关紧要。
回答by kariato
This of course will not work in SQL-Server 2000 but in a modern database where you don't want to create a permanent table. You can use a table variable instead creating a table so you can left join the data try this. Change the DAY to HOUR etc to change the increment type.
这当然不会在 SQL-Server 2000 中工作，但在您不想创建永久表的现代数据库中。您可以使用表变量而不是创建表，这样您就可以左连接数据试试这个。将 DAY 更改为 HOUR 等以更改增量类型。
declare @CalendarMonths table (date DATETIME, PRIMARY KEY (date)
)
DECLARE
@basedate DATETIME,
@offset INT
SELECT
@basedate = '01 Jan 2014',
@offset = 1
INSERT INTO @CalendarMonths SELECT @basedate
WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)
BEGIN
INSERT INTO @CalendarMonths SELECT DATEADD(HOUR, @offset, date) FROM @CalendarMonths where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP
SELECT @offset = @offset + @offset
END
回答by AjV Jsy
A starting point of a useful kludge to specify a range or specific list of dates:
指定范围或特定日期列表的有用 kludge 的起点：
SELECT *
FROM
(SELECT CONVERT(DateTime,'2017-1-1')+number AS [Date]
FROM master..spt_values WHERE type='P' AND number<370) AS DatesList
WHERE DatesList.Date IN ('2017-1-1','2017-4-14','2017-4-17','2017-12-25','2017-12-26')
You can get 0 to 2047 out of master..spt_values WHERE type='P', so that's five and a half year's worth of dates if you need it!
你可以从 0 到 2047 master..spt_values WHERE type='P'，所以如果你需要的话，这是五年半的日期！
回答by JNK
Tested below and it works, though it's a bit convoluted.
在下面进行了测试并且它有效，尽管它有点令人费解。
I assigned arbitrary values to the dates for the test.
我为测试日期分配了任意值。
DECLARE @SD smalldatetime,
@ED smalldatetime,
@FD smalldatetime,
@LD smalldatetime,
@Mct int,
@currct int = 0
SET @SD = '1/15/2011'
SET @ED = '2/02/2012'
SET @FD = (DATEADD(dd, -1*(Datepart(dd, @SD)-1), @sd))
SET @LD = (DATEADD(dd, -1*(Datepart(dd, @ED)-1), @ED))
SET @Mct = DATEDIFF(mm, @FD, @LD)
CREATE TABLE #MyTempTable (FoM smalldatetime, Trials int, Sales money)
WHILE @currct <= @Mct
BEGIN
INSERT INTO #MyTempTable (FoM, Trials, Sales)
VALUES
(DATEADD(MM, @currct, @FD), 0, 0)
SET @currct = @currct + 1
END
SELECT * FROM #MyTempTable
DROP TABLE #MyTempTable
回答by Sia
For SQL Server 2000, this stackoverflow postlooks promising for a way to temporarily generate dates calculated off of a start and end date. It's not exactly the same but quite similar. This posthas a very in-depth answer on truncating dates, if needed.
对于 SQL Server 2000，这个stackoverflow 帖子看起来很有希望提供一种临时生成从开始日期和结束日期计算出来的日期的方法。它不完全相同，但非常相似。 如果需要，这篇文章对截断日期有非常深入的回答。
In case anyone stumbles on this question and is working in PostgreSQL instead of SQL Server 2000, here is how you might do it there...
万一有人偶然发现这个问题并且正在使用 PostgreSQL 而不是 SQL Server 2000，这里是你可以在那里做的方法......
PostgreSQL has a nifty series generating function. For your example, you could use this series of all days instead of generating an entire calendar table, and then do groupings and matchups from there.
PostgreSQL 有一个漂亮的系列生成函数。对于您的示例，您可以使用这一系列的所有天，而不是生成整个日历表，然后从那里进行分组和匹配。
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
dates
------------
2004-02-05
2004-02-12
2004-02-19
(3 rows)
SELECT * FROM generate_series('2008-03-01 00:00'::timestamp,
'2008-03-04 12:00', '10 hours');
generate_series
---------------------
2008-03-01 00:00:00
2008-03-01 10:00:00
2008-03-01 20:00:00
2008-03-02 06:00:00
2008-03-02 16:00:00
2008-03-03 02:00:00
2008-03-03 12:00:00
2008-03-03 22:00:00
2008-03-04 08:00:00
(9 rows)
I would also look into date_truncfrom PostgreSQL using 'month' for the truncator field to maybe refactor your original query to easily match with a date_trunc version of the calendar series.
我还将查看PostgreSQL 中的date_trunc使用 'month' 作为 truncator 字段，以便重构原始查询以轻松匹配日历系列的 date_trunc 版本。
回答by davidofmorris
Create a table variable containing a date for each month in a year:
创建一个包含一年中每个月的日期的表变量：
declare @months table (reportMonth date, PRIMARY KEY (reportMonth));
declare @start date = '2018', @month int = 0; -- base 0 month
while (@offset < 12)
begin
insert into @reportMonths select dateAdd(month, @offset, @start);
select @offset = @offset + 1;
end
回答by user6619957
select top (datediff(D,@start,@end)) dateadd(D,id-1,@start)
from BIG_TABLE_WITH_NO_JUMPS_IN_ID
回答by Brandon Barkley
declare @start datetime
set @start = '2016-09-01'
declare @end datetime
set @end = '2016-09-30'
create table #Date
(
table_id int identity(1,1) NOT NULL,
counterDate datetime NULL
);
insert into #Date select top (datediff(D,@start,@end)) NULL from SOME_TABLE
update #Date set counterDate = dateadd(D,table_id - 1, @start)
The code above should populate the table with all the dates between the start and end. You would then just join on this table to get all of the dates needed. If you only needed a certain day of each month, you could dateadd a month instead.
上面的代码应该用开始和结束之间的所有日期填充表格。然后，您只需加入此表即可获取所需的所有日期。如果您只需要每个月的某一天，您可以使用 dateadd 来代替。
