Create mysql date range

Question

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

it's possible without using procedure, see here http://stackoverflow.com/questions/2157282/generate-days-from-date-range — mathheadinclouds, Nov 28 '14 at 05:59

score 11 · Accepted Answer · answered Jan 19 '11 at 15:56

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. ;-)

@Joe: Not a problem, glad to help! ;-) – Brad Christie Jan 19 '11 at 16:16 — Brad Christie, Jan 19 '11 at 16:16

Joe Stefanelli · Answer 2 · 2011-01-19T15:27:16.187

1

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;

edited Jan 19 '11 at 15:27

answered Jan 19 '11 at 15:10

Joe Stefanelli

132,803
19
237
235

@Joe: `DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. ` (per [DECLARE syntax](http://dev.mysql.com/doc/refman/5.0/en/declare.html) -- think it needs to be a procedure, though you're on the right track) – Brad Christie Jan 19 '11 at 15:21
@Joe: I still get a syntax error at the while condition. Does this work on your end? – Brad Christie Jan 19 '11 at 15:32
@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. – Joe Stefanelli Jan 19 '11 at 15:38
@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. – Brad Christie Jan 19 '11 at 15:40

score 0 · Answer 3 · answered Mar 22 '13 at 08:26

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

score 0 · Answer 4 · answered Jun 20 '22 at 08:53

0

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`;

answered Jun 20 '22 at 08:53

Nibbik

11
2

Create mysql date range

4 Answers4

Linked