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

问题描述

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

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 社区开放了这个.今天是 2010 年 1 月 29 日.我可以运行什么查询来提供一个包含单个日期列的表，其值从 2009 年 11 月 1 日到 2010 年 1 月 28 日(含)?2 月 1 日，它应该给我从 2009 年 12 月 1 日到 2010 年 1 月 31 日的每个日期.

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.

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

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

我知道我可以从 sysibm.sysdummy1 中选择 CURRENT DATE (或者对于 Oracle bods 选择 dual)，但我不知道如何立即选择一个没有物理后备表的日期范围.

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.

推荐答案

这只是两个日期之间的连续天数，但我发布的内容是为了说明您可以通过提供限制来消除递归错误.

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

<小时>

来自 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;

在 2 月 2 日^nd 运行时输出:

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.

这篇关于如何获得从两个月前的第一天到昨天的日期表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！