Date Query

Databases
1

Hi guys

I’m wondering if this is possible.

In my events table I have the following columns: Start Date, End Date and 7 columns for the days of the week which are set as INT with the value of 0 or 1 if true.

If there is a single entry in the table with a start date of the 18th Nov 2010 and end date of 30th Dec 2010 and the Thursday field is flagged as 1 would it be possible to return a resultset which would display each Thursday between and including the two dates?

Thanks in advance!
Leigh

2

yes, this is really easy to do, provided you have the following table in your database –


CREATE TABLE numbers
( n INTEGER NOT NULL PRIMARY KEY );
INSERT INTO numbers VALUES
(0),(1),(2),(3),(4),(5),(6),...

where you would have as many numbers in the table as the largest range of dates that you want to generate

once you have that set up, please do a SHOW CREATE TABLE for your table and i’ll show you what the query would look like