How to create date columns automatically

Question

How to create date columns automatically from start date to end date create table demo table ( Id int primary key identity(11) 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

Suyambu Kesavan · Answer

create table demotable ( Id int primary key identity(11) 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

Manas Mohapatra · Answer

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

madhu goud · Answer

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.

Manas Mohapatra · Answer

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.