starhu
asked on
MySql create a table - each day a record
Hello,
I need to create a table (or insert in a table) as follows
- I know the starting date and ending date e.g. 2011.10.12 and 2011.10.16
- I should generate a record for each day between the two dates.
For example the output of the example above would be:
2011.10.12
2011.10.13
2011.10.14
2011.10.15
2011.10.16
Of course in the real situation the dates can be for example from 2007.01.01 to 2010.10.12 so I can't do this manually.
Thank you
I need to create a table (or insert in a table) as follows
- I know the starting date and ending date e.g. 2011.10.12 and 2011.10.16
- I should generate a record for each day between the two dates.
For example the output of the example above would be:
2011.10.12
2011.10.13
2011.10.14
2011.10.15
2011.10.16
Of course in the real situation the dates can be for example from 2007.01.01 to 2010.10.12 so I can't do this manually.
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE TABLE number (num_value INT);
INSERT INTO number (num_value) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
SELECT ADDDATE('2009-01-01', NumberList.id) AS `DateBetweenRange` FROM
(SELECT n1.num_value + n10.num_value*10 + n100.num_value*100 AS Id
FROM number n1 CROSS JOIN number AS n10 CROSS JOIN number AS n100) AS NumberList
WHERE ADDDATE('2009-01-01', NumberList.id) <= '2009-01-13';
I can make this into a procedure if you want, which will avoid the table creation.