Try this:
DECLARE @dtmin date
DECLARE @dtmax date
DECLARE @dynsql nvarchar(3000)
DECLARE @colname varchar(20)
SET @dynsql = N'CREATE TABLE trial('
SET @dtmin = {d '2013-07-15'}
SET @dtmax = {d '2013-07-18'}
DECLARE @currdate date
SET @currdate = @dtmin
WHILE (@currdate <= @dtmax)
BEGIN
SET @colname = DATENAME(month, @currdate) + CONVERT(varchar, DATEPART(day, @currdate))
SET @dynsql = @dynsql + @colname + ' varchar(10)'
IF (@currdate < @dtmax)
BEGIN
SET @dynsql = @dynsql + ', '
END
ELSE
BEGIN
SET @dynsql = @dynsql + ')'
END
SET @currdate = DATEADD(day, 1, @currdate)
END
EXEC sp_executesql @dynsql
P.S.: I don't know the reason of your request, but generally is not correct create table in this way because the information you want to put into columns, should be put in the rows.
Example:
If I want to store sales day by day, the correct table, I named SALES, is:
SALES (id varchar(36), dt datetime, amount decimal(19,2))
instead of
SALES (ID VARCHAR(36), dt1 decimal(19,2), dt2 decimal(19,2)... and so on)
Because if you want to change your period you must rewrite your table, if you want query your table you must write several queries for each situation. It's very hard work on this table.
Please, reconsider you choice.
Have a nice day.
