How to create date columns automatically

11y1.1k

How to create date columns automatically from start date to end date

create table demo table

(

Id int primary key identity(1,1),

Name varchar(50),

StartDate date,

EndDate date

)

i have given start date 1/7/2015 and end date 1/10/2015 , in out put i want to add extra (month wise )columns automatically ,like

ID Name Start Date End Date 1/7/2015 1/8/2015 1/9/2015 1/10/2015

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suyambu Kesavan11y ago
Accepted answer
create table demotable
(
Id int primary key identity(1,1),
Name varchar(50),
StartDate date,
EndDate date
)

declare @dates int, @StartDate datetime, @EndDate datetime

---Here, we assume that,
Select @StartDate = convert(datetime, '1/7/2015'), @endDate = convert(datetime,'1/10/2015')

select @dates = datediff (dd,@StartDate ,@Enddate)

---Now add columns by dynamically with while loop

While (@dates >0)
begin
alter table demotable add [Columnname] datetime
select @dates = @dates - 1
end

--Here column name might be dynamically, you can write in string and execute as Dynamic query
0
Manas Mohapatra11y ago
Make your unique because it is creating inside loop. It is already a columnname column is in your table.
Query:

-- Declaring variable for looping
declare @temp varchar(10);
declare @tempSql varchar(200);
-- Looping to create new columns in table
While (@dates >0)
begin
   SELECT @temp = CONVERT(VARCHAR(10), @dates, 101);

   SET @tempSql = 'alter table demotable add [' + @temp +'] varchar(10)';
   EXEC (@tempSql);

   select @dates = @dates - 1;

end
0
11y ago
Thanks for your help sir i am getting following error sir Column names in each table must be unique. Column name 'Columnname' in table 'demotable' is specified more than once.
0
Manas Mohapatra11y ago
Hi Madhu,

You need to execute following command to create columns.
ALTER TABLE [dbo].[ExistingTable] ADD [NewColumn] VARCHAR(50) NULL;

If you want then write that command inside a stored procedure which will create dynamically columns as per start date and end date.

Not sure why are looking dynamically add columns if a user gives a date difference between 1 year - think how many columns will create in tables.

Note: If you want to display then you can modify in your code project.
0