www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] Prefill column with date value

  1. #1
    Join Date
    Apr 2013
    Posts
    2

    resolved [RESOLVED] Prefill column with date value

    Hello,
    Need some help because i haven't done any programing in years and need to solve some small problem i've fixed before but i have no idea how i did it back then.

    In mysql, i have a table where one column (set as date, also primary key) is prefilled with all dates of the year (standard yyyy-mm-dd format, also for many years because the php is based on the values existing already there, so no autofills).
    Stupidly i have prefilled the column up to the end of 2013. Now i need to "refill" the column for at least 50 more years so i don't have to think about it for a while But i have idea how? I guess i have to first generate a column with all dates from 1 jan 2014 to X, then insert the values in the existing table/column.

    Any help is highly appreciated! Any programing skills i've had are long long gone

  2. #2
    Join Date
    Sep 2011
    Posts
    59
    something like that
    Code:
    DECLARE count INT DEFAULT 1;
     WHILE count < 2000 DO
       insert into youtTable (dateValue) values(DATE_ADD('12/1/2013',count 45 MONTH))
       SET count = count + 1;
     END WHILE;

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Thank you! Got some errors on that and don't know why.. Found one other solution though which worked for me, so i'll post it for others with same problem (link to the original thread here). It creates far longer list than the dates i've chosen at the end, if somebody needs further ahead dates than 2099


    Code:
    SELECT * FROM 
    (SELECT ADDDATE('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date FROM
     (SELECT 0 t0 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 t1 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 t2 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 t3 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 t4 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 '2014-01-01' AND '2099-12-31'

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles