Can't seem to find the answer I am looking for.
I want to create a range of dates from 2010-11-01 to 2015-01-01 in a table.
2010-11-01 2010-11-02 2010-11-03 etc...
Column datatype is 'Date'
Thanks
2
-
please see the answer I posted.Brad Christie– Brad Christie2011-01-19 15:56:37 +00:00Commented Jan 19, 2011 at 15:56
-
it's possible without using procedure, see here stackoverflow.com/questions/2157282/…mathheadinclouds– mathheadinclouds2014-11-28 05:59:56 +00:00Commented Nov 28, 2014 at 5:59
Add a comment
|
4 Answers
DROP PROCEDURE IF EXISTS datespopulate;
DELIMITER |
CREATE PROCEDURE datespopulate(dateStart DATE, dateEnd DATE)
BEGIN
WHILE dateStart <= dateEnd DO
INSERT INTO datetable (d) VALUES (dateStart);
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
|
DELIMITER ;
CALL datespopulate('2010-11-01','2015-01-01');
Note I named my table "datetable" and the column is named "d", but feel free to change this. Works fine on my end, let me know if you run in to an issue.
Kudos to Joe for getting the ball rolling. ;-)
1 Comment
Brad Christie
@Joe: Not a problem, glad to help! ;-)
You could certainly take the brute force approach.
set @d = cast('2010-11-01' as date);
while (@d < '2015-01-02') do
insert into YourTable
(YourColumn)
values
(@d);
set @d = date_add(@d, interval 1 day);
end while;
4 Comments
Brad Christie
@Joe:
DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. (per DECLARE syntax -- think it needs to be a procedure, though you're on the right track)Brad Christie
@Joe: I still get a syntax error at the while condition. Does this work on your end?
Joe Stefanelli
@Brad: I don't have a place to test at this time. As you indicated earlier, this may need to be encapsulated in a proc.
Brad Christie
@Joe/@mrlayance: I'm working on it. I stopped once this answer was posted, but I'll play around and see if I can bypass that error.
Realize that this is an old thread- However, I found it useful and would like to suggest adding SET AUTOCOMMIT=0 to Brad's procedure. This will seriously increase the performance (On my system; from 2 hours to 4 seconds). More information is found here:
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html
Comments
With the new SEQUENCE engine in MariaDB, there is a possibility:
SELECT
DATE_ADD(
CAST('2022-06-01' AS DATE),
INTERVAL `s1`.`seq` DAY
) AS `dates`
FROM `seq_0_to_364` AS `s1`;
