如何从一个月的第一天，两个月前到昨天获得一份日期表？(How can I get a table of dates from the first day of the month, two month

如何从一个月的第一天，两个月前到昨天获得一份日期表？(How can I get a table of dates from the first day of the month, two months ago, to yesterday?)

我有一种情况，我通常会通过创建一个支线表（例如，五年前和未来一百年之间的每一个日期）来进行查询，但不幸的是，这个特殊的工作不允许创建这样的表格。

所以我要向SO社区开放。 今天是2010年1月29日。我可以运行什么查询，它会给出一个包含单个日期列的表，其值范围从2009年11月1日到2010年1月28日（含）？ 在2月1日，它应该给我每个日期从2009年12月1日到2010年1月31日。

我正在使用DB2，但我很高兴看到任何其他解决方案，他们可能提供一个线索。

我知道我可以从sysibm.sysdummy1选择CURRENT DATE （或者对于Oracle bods来说是dual的）但是我不确定如何在没有物理支持表的情况下立即选择日期范围。

I have a situation that I would normally solve by creating a feeder table (for example, every date between five years ago and a hundred years into the future) for querying but, unfortunately, this particular job disallows creation of such a table.

So I'm opening this up to the SO community. Today is Jan 29, 2010. What query could I run that would give a table with a single date column with values ranging from Nov 1, 2009 through Jan 28, 2010 inclusive? On Feb 1, it should give me every date from Dec 1, 2009 through Jan 31, 2010.

I'm using DB2 but I'm happy to see any other solutions on the off-chance they may provide a clue.

I know I can select CURRENT DATE from sysibm.sysdummy1 (or dual for Oracle bods) but I'm not sure how to immediately select a date range without a physical backing table.

最满意答案

这只是在两个日期之间连续几天，但我发布以显示您可以通过提供限制来消除递归错误。

with temp (level, seqdate) as (select 1, date('2008-01-01') from sysibm.sysdummy1 union all select level, seqdate + level days from temp where level < 1000 and seqdate + 1 days < Date('2008-02-01') ) select seqdate as CalendarDay from temp order by seqdate

---

从pax更新：

这个答案实际上让我走上正轨。 您可以通过引入受常量限制的变量来消除警告。 上面的查询没有完全正确（并且日期错误，我将原谅），但是，因为它指出了问题解决方案，它赢得了奖品。

下面的代码是最终的工作版本（没有警告）：

WITH DATERANGE(LEVEL,DT) AS ( SELECT 1, CURRENT DATE + (1 - DAY(CURRENT DATE)) DAYS - 2 MONTHS FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT LEVEL + 1, DT + 1 DAY FROM DATERANGE WHERE LEVEL < 1000 AND DT < CURRENT DATE - 1 DAY ) SELECT DT FROM DATERANGE;

哪个输出，在2月2日运行时：

---------- DT ---------- 2009-12-01 2009-12-02 2009-12-03 : : : : 2010-01-30 2010-01-31 2010-02-01 DSNE610I NUMBER OF ROWS DISPLAYED IS 63 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL.

This just does sequential days between two dates, but I've posted to show you can eliminate the recursive error by supplying a limit.

with temp (level, seqdate) as (select 1, date('2008-01-01') from sysibm.sysdummy1 union all select level, seqdate + level days from temp where level < 1000 and seqdate + 1 days < Date('2008-02-01') ) select seqdate as CalendarDay from temp order by seqdate

---

Update from pax:

This answer actually put me on the right track. You can get rid of the warning by introducing a variable that's limited by a constant. The query above didn't have it quite right (and got the dates wrong, which I'll forgive) but, since it pointed me to the problem solution, it wins the prize.

The code below was the final working version (sans warning):

WITH DATERANGE(LEVEL,DT) AS ( SELECT 1, CURRENT DATE + (1 - DAY(CURRENT DATE)) DAYS - 2 MONTHS FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT LEVEL + 1, DT + 1 DAY FROM DATERANGE WHERE LEVEL < 1000 AND DT < CURRENT DATE - 1 DAY ) SELECT DT FROM DATERANGE;

which outputs, when run on the 2^nd of February:

---------- DT ---------- 2009-12-01 2009-12-02 2009-12-03 : : : : 2010-01-30 2010-01-31 2010-02-01 DSNE610I NUMBER OF ROWS DISPLAYED IS 63 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL.