Dynamic Date As Column Name in SQL Server

Question

On my select statement, I want to call to one of my columns as a dynamic date.

For example, something like:

SELECT <column_name> as CONVERT(DATE, GETDATE() - 1)
FROM <table_name>

This obviously doesn't work.

Any ideas?

Try SELECT CONVERT(DATE, GETDATE() - 1) AS <column_name> FROM <table_name> . Of course change the placeholder coulmn and table names to originals. — Abdul Rehman
– Abdul Rehman, Commented Apr 24, 2017 at 7:56
Chanukya, thank you for your answer. I'm afraid you didn't understand my problem. I wanted the dynamic date to be the column's name and not the record's value. — Itay Nagar
– Itay Nagar, Commented Apr 24, 2017 at 8:02
Then you could explain more, why you need a date as column name and provide some input data, expected result? — TriV
– TriV, Commented Apr 24, 2017 at 8:04
Imagine a matrix where there are 8 columns: 8 days ago, 7 days ago... 1 day ago. On the current state, I call them just like this ("8 days ago", "7 days ago"...) but I want to present the date itself instead — Itay Nagar
– Itay Nagar, Commented Apr 24, 2017 at 8:09

Kapil · Accepted Answer · 2017-04-24 08:30:33Z

1

Check dynamic query like below

declare  @ssql nvarchar(500)
set @ssql= N'Select Getdate() as ['+ Cast(CONVERT(DATE, GETDATE() - 1) as nvarchar(25))+'] ;'
exec sp_executesql @ssql

You can change the select with your column and table name

 declare  @ssql nvarchar(500)
 set @ssql= N'Select <colName> as ['+  Cast(CONVERT(DATE, GETDATE() - 1) as nvarchar(25))+'] from tableName ;'  
exec sp_executesql @ssql

For additional columns:

declare  @ssql nvarchar(500)
 set @ssql= N'Select <colName> as ['+  Cast(CONVERT(DATE, GETDATE() - 1) as nvarchar(25))+'],<colName2> as ['+  Cast(CONVERT(DATE, GETDATE() - 2) as nvarchar(25))+']
 ,<colName3> as ['+  Cast(CONVERT(DATE, GETDATE() - 3) as nvarchar(25))+'] from tableName ;'  
exec sp_executesql @ssql

edited Apr 24, 2017 at 8:30

answered Apr 24, 2017 at 8:15

Kapil

1,0876 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Itay Nagar Over a year ago

That's a progress! But if I want it to be a matrix (couple of columns and not only one)?

TriV Over a year ago

That's why you need some sample data @Itay Nagar. No one can imagine what you want

Ferdinand · Accepted Answer · 2017-04-24 08:03:13Z

0

Why not storing data with fixed column name "DATE" ? and using simple WHERE date=CONVERT(DATE, GETDATE() - 1) ?

answered Apr 24, 2017 at 8:03

Ferdinand

212 bronze badges

1 Comment

Itay Nagar Over a year ago

Because the full query should return a matrix, something like: <n days ago> <n-1 days ago> ... <1 day ago> <group 1> <group 2> ... <group n>

Collectives™ on Stack Overflow

Dynamic Date As Column Name in SQL Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related