CODEWITHSUNDEEP
sqldatetimedynamic-sql
Johan Hjalmarsson
Johan Hjalmarsson

Reputation: 3493

sql dates as column name

I want a table with columns named after dates.

example:

SELECT * AS GETDATE() FROM mytable;

From what I've read on the internet, it looks as though I will need to use dynamic SQL (?) something in the lines of this:

DECLARE @dt smalldatetime
SET @dt = GETDATE()
DECLARE @str varchar(100)
SET @str = 'SELECT * AS ' + convert(varchar(100), GETDATE(), 120) + ' FROM mytable'
EXEC(@str);

But this doesn't work. says "incorrect syntax near the keyword 'AS'

Upvotes: 0

Views: 4866

Answers (2)

Msyma
Msyma

Reputation: 57

Try this:

DECLARE @dt smalldatetime

SET @dt = GETDATE()

DECLARE @str varchar(100)

SET @str = 'SELECT Item AS '+'''' + convert(varchar(100), GETDATE(), 120) +'''' + ' FROM mytable'
EXEC(@str);

Upvotes: 0

Johan Hjalmarsson
Johan Hjalmarsson

Reputation: 3493

All I had to do was change from SELECT * to SELECT [the thing i needed]

DECLARE @dt smalldatetime
SET @dt = GETDATE()
DECLARE @str varchar(100)
SET @str = 'SELECT Item AS ' + convert(varchar(100), GETDATE(), 120) + ' FROM mytable'
EXEC(@str);

this works.

Upvotes: 1

Related Questions