Generate Dynamic Date Columns as per input.

Dwarkesh Vajjala

DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-07';


-- like a temprary table must be create with columns as.

-- thes start date and end date is dynamic. so it can go 7 columns , 12 columns , 70 columns..etc

_____________________________________________________________________________________________
id |2023/01/01 | 2023/01/02 | 2023/01/03 | 2023/01/04 | 2023/01/05 | 2023/01/06 | 2023/01/07 |
---------------------------------------------------------------------------------------------

so i want dynamic generated columns for the temporary table. where the StartDate and EndDate are dynamic example 7,14,90 days.

1 Reply

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

Amit Mohanty2y ago

Accepted answer

Check the below code.

DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-07';
DECLARE @SQL NVARCHAR(MAX);
DECLARE @CurrentDate DATE;

SET @SQL = 'CREATE TABLE ##TempTable (id INT';

SET @CurrentDate = @StartDate;
WHILE @CurrentDate <= @EndDate
BEGIN
    SET @SQL = @SQL + ', [' + CONVERT(VARCHAR, @CurrentDate, 111) + '] NVARCHAR(50)';
    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
END;

SET @SQL = @SQL + ');';

PRINT @SQL;
EXEC sp_executesql @SQL;

SELECT * FROM ##TempTable