CODEWITHSUNDEEP
sqlsql-server
Itay Nagar
Itay Nagar

Reputation: 53

Dynamic Date As Column Name in SQL Server

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?

Upvotes: 3

Views: 11924

Answers (2)

Kapil
Kapil

Reputation: 987

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

Upvotes: 1

Ferdinand
Ferdinand

Reputation: 21

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

Upvotes: 0

Related Questions