SQL column name with function

Question

I want to create 3 new columns with their names reffering to some date varibales and this is not possible to write them like this. So the first column name should be YEAR2022, 2nd column YEAR2021 and 3rd column YEAR2020.

Can you please give me an idea how to write this?

select column1*2 as CONCAT('YEAR',YEAR(DATEADD(YY,0,GETDATE()))),
        column1*3 as CONCAT('YEAR',YEAR(DATEADD(YY,-1,GETDATE()))),
        column1*4 as CONCAT('YEAR',YEAR(DATEADD(YY,-2,GETDATE()))) FROM table1

The error that I get is:

Incorrect syntax near 'YEAR'.

An alias cannot be an expression, it must be a literal. If you need dynamically named columns this is normally the sign of a design flaw, in my opinion. — Thom A
– Thom A ♦, Commented Jul 11, 2022 at 11:26
There is no need for a loop here, @Alen.Toma . There are, in fact, very few reasons to ever use a WHILE loop (T-SQL doesn't support For Loops) in SQL; it is a set based language and set based methods are almost always more performant. — Thom A
– Thom A ♦, Commented Jul 11, 2022 at 11:27
Most report writing tools make this a trivial task - is that an option? And why do you need that complicated dateadd logic? You can simply use the YEAR() function and subtract 1 or 2 from it - far easier to read and understand. Adding or subtracting 0 (zero) from a given value serves no useful purpose. — SMor
– SMor, Commented Jul 11, 2022 at 12:13

Thom A · Accepted Answer · 2022-07-11 11:41:34Z

As I mentioned in my comment, an alias cannot be an expression, it has to be a literal. As such the expressions you have tried to use are not allowed and generate syntax errors.

Normally, this sort requirement is the sign of a design flaw, or that you're trying to do something that should be in the presentation in the SQL layer. I'm going to assume the latter here. As a result, instead you should use static names for the columns, and then in your presentation layer, control the name of the columns there, so that when they are presented to the end user they have the names you want (for today that would be YEAR2022, YEAR2021 and YEAR2020). Then your query would just look like this:

select column1*2 AS ThisYear,
       column1*3 AS YearPrior,
       column1*4 AS Year2Prior
FROM dbo.table1;

How you change the names of the columns in your presentation layer is a completely different question (we don't even know what language you are using to write your application). If you want to ask about that, I suggest asking a new question (only if after you've adequately researched the problem and failed to find a solution), so that we can help you there.

Note that Though you can achieve a solution via dynamic SQL, I would strongly suggest it is the wrong solution here, and thus why I haven't given an answer providing a demonstration.

Collectives™ on Stack Overflow

SQL column name with function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related