CODEWITHSUNDEEP
sql-server
Vaibhav
Vaibhav

Reputation: 303

Increment date in date field

I have a table with 365 records,this table also have one date column with null value.I want to insert date in all the rows with increment of 1 day with current date. Ex: current date in 1st column and in 2nd column it should be next day's and so on...difference should be of 24hrs between all the date records.

Upvotes: 0

Views: 173

Answers (2)

Tim Burch
Tim Burch

Reputation: 1103

A stored routine with a cursor inside should do the job:

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_ascending_dates` $$
CREATE PROCEDURE `insert_ascending_dates`()
BEGIN

declare done int default false;
declare t_id int;
declare c cursor for select [id] from [table];
declare continue handler for not found set done = true;

set @dte = date();
set @strSql = 'update [table] set [date] = ? where [id] = ?';
prepare stmt from @strSql;

open c;
read_loop: loop
  fetch c into t_id;
  if done then
    leave read_loop;
  end if;
  set @tId = t_id;
  execute stmt using @dte, @tId;
  set @dte = date_add(@dte, interval 1 day);
end loop;
close c;

deallocate prepare stmt;

END $$

DELIMITER ;

After creating the routine, you'll need to call it as follows:

call insert_ascending_dates();

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can use this update query:

UPDATE tbl SET Col2 = DATEADD(d, 1, Col1)

Upvotes: 1

Related Questions