0

I want to add every date of the year into my database table. How could I to do that ?

I was looking in google but there is no clue.

Please help me with some insert query to do that.

This is my table structure.

id | date
---------
1 | 2017-01-01
Antonio
  • 755
  • 4
  • 14
  • 35
  • check this example http://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query – krishn Patel Jan 27 '17 at 06:58

3 Answers3

4

Updated after question clarified.

Here is an updated version, try this out. For the while loop to work you need to create it as a stored procedure. Create the stored procedure like so

DELIMITER $$

create PROCEDURE insert_year_dates() 
BEGIN 
    SET @t_current = NOW(); 
    SET @t_end = DATE_ADD(NOW(), INTERVAL 1 YEAR); 
    WHILE(@t_current< @t_end) DO 
        INSERT INTO day (day) VALUES (@t_current); 
        SET @t_current = DATE_ADD(@t_current, INTERVAL 1 DAY); 
    END WHILE; 
END;

Then call that stored procedure using 

CALL insert_year_dates()
Jhorra
  • 6,233
  • 21
  • 69
  • 123
  • could you give me complete insert query with your answer and I want date format like this `yyyy-mm-dd` – Antonio Jan 27 '17 at 07:01
  • I've updated the answer, but as I said, without know what your table structure is I can't give you an exact query. – Jhorra Jan 27 '17 at 07:04
  • I just edited my questions with table structure and it just simpe table with field `id` and `date` – Antonio Jan 27 '17 at 07:07
  • Take my answer and replace `table` with your table name and replace `field` with your field name. – Jhorra Jan 27 '17 at 07:08
  • I got this error. `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'YEAR, 1))' at line 1` – Antonio Jan 27 '17 at 07:10
  • What does your query look like? – Jhorra Jan 27 '17 at 07:12
  • INSERT INTO `day` (`date`) VALUES (DATE_ADD(CURDATE(), YEAR, 1)) – Antonio Jan 27 '17 at 07:13
  • 1
    Are you sure `DATE_ADD(NOW(), YEAR, 1)` is valid? I only know `DATE_ADD(NOW(), INTERVAL 1 YEAR)` – t.niese Jan 27 '17 at 07:15
  • Sorry, I fixed my answer. I confused my MySQL syntax with my Sql Server syntax. I've corrected it. – Jhorra Jan 27 '17 at 07:17
  • with `DATE_ADD(NOW(), INTERVAL 1 YEAR)` there is no error message but it just insert today's date in 2018 not all date in one year. @t.niese – Antonio Jan 27 '17 at 07:24
  • If you run `SELECT NOW(); SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);` what do you get? – Jhorra Jan 27 '17 at 07:28
  • if I run `SELECT NOW();` I got `2017-01-27 15:29:03` and if I run `SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);` I got `2018-01-27 15:29:58` – Antonio Jan 27 '17 at 07:30
  • Well there you go. the query I gave you should work as expected. The second one you ran returns a value 1 year into the future. That's exactly what the query you're trying to run is inserting. – Jhorra Jan 27 '17 at 07:33
  • and what is the query could I use ? – Antonio Jan 27 '17 at 07:35
  • Are you asking for a query that will insert an entry for every date of the year? I'm assuming English is your second language. Your question sounds like you want to insert a date one year from now. – Jhorra Jan 27 '17 at 07:36
  • Ya, I mean every date of the year not a date one year from now. – Antonio Jan 27 '17 at 07:40
  • I got this error `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2` – Antonio Jan 27 '17 at 07:52
  • Ok, I had to install MySQL real quick to get all the syntax right, but the updated answer should now work. – Jhorra Jan 27 '17 at 08:25
  • How could I create store procedure and call it on phpmyadmin ? – Antonio Jan 27 '17 at 08:30
  • run each of those two queries. I built and ran them in phpmyadmin – Jhorra Jan 27 '17 at 08:31
  • Okay, that's works. Thank you so much. – Antonio Jan 27 '17 at 08:34
1

you can try it first get all date between to date and add it. 

insert into day (date)
    select * from 
    (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    where selected_date between '2017-01-01' and '2017-12-31'
krishn Patel
  • 2,579
  • 1
  • 19
  • 30
  • I got this error `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as select * from (select adddate('1970-01-01',t4.i*10000 + t3.i*1000' at line 1` – Antonio Jan 27 '17 at 07:41
  • oh remove **as**. check it now – krishn Patel Jan 27 '17 at 08:42
  • Can you help me with this questions [Resul query with if condition](http://stackoverflow.com/questions/41929355/return-query-result-with-if-condition) – Antonio Jan 30 '17 at 06:15
  • I can not understand your if condition. so first put table with all possible value 0 and 1. and show what is the imp of date – krishn Patel Jan 30 '17 at 06:22
  • I just edited my questions, please look at that. – Antonio Jan 30 '17 at 06:39
-1

In mysql you can choose the column to only be filled with a date format. You can insert the data as '2017' or '17'. It will work both fine.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stan van Heertum
  • 85
  • 11