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
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 2nd 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.