Field Alias as Function

I want to change the alias of the fields below to the current month and the
month +1 ie datename(month, getdate()) and datename(month,
dateadd(mm,1,getdate())), so instead of '1' and '2' I have January and
February
Select
case when p1.ExpectedDeliveryDate >= @.Start and p1.ExpectedDeliveryDate <=
dateadd(dd,-1,dateadd(mm,1,@.Start)) then convert(int,ROUND(Q1.TotalValue /
1000, 0)) end as '1',
case when p1.ExpectedDeliveryDate >= dateadd(dd,-1,dateadd(mm,1,@.Start)) and
p1.ExpectedDeliveryDate <= dateadd(dd,-1,dateadd(mm,2,@.Start)) then
convert(int,ROUND(Q1.TotalValue / 1000, 0)) end as '2',
Can anyone please advise how i can acheive this
Regards
JohnJohn wrote:
> I want to change the alias of the fields below to the current month and th
e
> month +1 ie datename(month, getdate()) and datename(month,
> dateadd(mm,1,getdate())), so instead of '1' and '2' I have January and
> February
> Select
> case when p1.ExpectedDeliveryDate >= @.Start and p1.ExpectedDeliveryDate <=
> dateadd(dd,-1,dateadd(mm,1,@.Start)) then convert(int,ROUND(Q1.TotalValue /
> 1000, 0)) end as '1',
> case when p1.ExpectedDeliveryDate >= dateadd(dd,-1,dateadd(mm,1,@.Start)) a
nd
> p1.ExpectedDeliveryDate <= dateadd(dd,-1,dateadd(mm,2,@.Start)) then
> convert(int,ROUND(Q1.TotalValue / 1000, 0)) end as '2',
> Can anyone please advise how i can acheive this
> Regards
> John
A query can't change aliases dynamically at runtime unless you
construct the query itself dynamically (dynamic SQL).
Why is the alias important to you? You don't have to use that column
name when you display or print the result so it ought to be easier to
retrieve a fixed column name and just display the month name in its
place in the client app or report. Most reporting tools for example
will allow you to add whatever dynamic column headings you require.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David, took you advise and formated column heading in report
John
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138357083.736038.251280@.g44g2000cwa.googlegroups.com...
> John wrote:
> A query can't change aliases dynamically at runtime unless you
> construct the query itself dynamically (dynamic SQL).
> Why is the alias important to you? You don't have to use that column
> name when you display or print the result so it ought to be easier to
> retrieve a fixed column name and just display the month name in its
> place in the client app or report. Most reporting tools for example
> will allow you to add whatever dynamic column headings you require.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>