create procedure P_ShiftDate
   @str_weeks nvarchar(5),
   @move_budget int
as
   set nocount on;
   declare
   --@str_weeks nvarchar(5), -- for tests
   @stmt nvarchar(3000),
   @days int,
   @c_null_date nvarchar(255),
   @table_name nvarchar(255),
   @column_name nvarchar(255),
   @test_str nvarchar(255);

   if @days > 0
       BEGIN
	   set @c_null_date = CONVERT([datetime2],'01-01-1970',(110)); -- big bang date in python
	   --set @str_weeks = 3; -- for tests
	   set @days = convert(int, @str_weeks) * 7;     -- convert weeks in days
	   set @test_str = 'CONVERT(datetime2,''' + @c_null_date + ''',(126))'; -- 126 is ISO format
   
	   declare cur CURSOR LOCAL FAST_FORWARD TYPE_WARNING
	   FOR select c.TABLE_NAME as table_name, c.COLUMN_NAME as column_name
					from INFORMATION_SCHEMA.COLUMNS c, dt415 dt
					where 1 = 1
					and DATA_TYPE = 'DATETIME2'
					and c.TABLE_NAME = dt.DI000216                              -- only planta tables
					and ( dt.di000266 in ('Q5B')                                -- only tables that belong to Q5B 
						  or dt.DI000216 in ('DT511','DT516','DT525','DT526')   -- or relevant tables from other schemas
						)
					and dt.DI000216 not in ('DT418','DT430','DT468');           -- but not calendar tables from Q5B
	   open cur
		  fetch next from cur into @table_name, @column_name;

	   while @@FETCH_STATUS = 0
		  begin
			 set @stmt = 'update ' + @table_name + ' set ' + @column_name + ' = dateadd(day, ' + @days + ' , ' + @column_name + ')' + 
								' where ' + @column_name + ' is not null and ' + @column_name + ' > ' + @test_str + ';'
		 
			 exec (@stmt);
		 
			 fetch next from cur into @table_name, @column_name;
		  end;
	   close cur;
	   deallocate cur;
	   END

   -- move cost and budget per year data by one year
   if @move_budget = 1  
     BEGIN
	 -- move project years
     alter table DT282 NOCHECK CONSTRAINT "FKDT282ID861";
     update dt861 set DI023062 = DI023062+1, UUID=newid();
     update dt282 set DI060472 = DI060472+1, UUID=newid();
     alter table DT282 CHECK CONSTRAINT "FKDT282ID861";
     -- move project report years
     alter table DT285 NOCHECK CONSTRAINT "FKDT285ID835";
     update dt835 set DI029231 = DI029231+1, UUID=newid();
     update dt285 set DI060388 = DI060388+1, UUID=newid();
     alter table DT285 NOCHECK CONSTRAINT "FKDT285ID835";
	 -- move portfolio years
	 alter table DT283 NOCHECK CONSTRAINT "FKDT283ID859";
	 update dt859 set DI023053 = DI023053+1, UUID=newid();
	 update dt283 set DI060345 = DI060345+1, DI060429 = DI060429+1, UUID=newid();
	 update dt286 set DI061326 = DI061326+1, UUID=newid();
	 alter table DT283 CHECK CONSTRAINT "FKDT283ID859";
	 -- move portfolio report years
	 alter table DT092 NOCHECK CONSTRAINT "FKDT092ID091";
	 update dt091 set period_id = period_id+1, UUID=newid();
	 update dt092 set period_id = period_id+1, UUID=newid();
	 alter table DT092 CHECK CONSTRAINT "FKDT092ID091";
	 END