On Wed, 03 Oct 2007 10:37:38 -0700, Mike Harrison wrote:
> On Oct 3, 5:05 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
>> Sure. Make a table with all the dates you want to list in it. Join
>> your real table to it with an outer join.
>>
>
> Thanks for the responses. So what's the easiest way to create a table
> of dates? Is there a way to automate it or have I to enter it all
> manually?
>
> For example, I would like a table of dates from 2007-09-01 to
> 2008-12-31.
>
> I guess one way would be to create the column of dates in Excel or
> another spreadsheet, export to a file and then LOAD DATA INFILE. But
> is there an easier way to do it?

Any number of application programs would do it simply, with loop over an
INSERT query. It's just not something that SQL is well-suited for. It's
something you only have to do ONCE, after all, if you make a big enough
table. For anything else, you just do something like

INSERT INTO my_dates (my_date)
SELECT date_col
FROM master_dates
WHERE date_col >= DATE('2007-09-01') AND
date_col <= DATE('2008-12-31');

Your master date table can run for 100 years with less than 36,000 rows,
after all.

--
For every subject you can think of there are at least 3 web sites.
The owners of these web sites know each other and at least one of
them hates at least one of the others.
-- mnlooney's view of Skif's Internet Theorem