Post reply

expanding the keyword AS

  • July 8, 2015 at 5:22 am

    #302213

    The keyword AS normally creates a column name for an alias.

    Can someone please tell me how to do the following as a column name:

    month1: + convert( varchar(10),GETDATE(),110)

    so that the column name displays "month1:" + today's date?

    I've tried to declare a varchar, but "AS @varchar" gives me an error.

    It should be displayed like the following example:

    Select 1 AS Month1: 07/07/2015 where the part after the AS keyword now becomes the column name.

    ----------------------|

    Month1: 07/07/2015|

    ----------------------|

    1 |

    ----------------------|

  • July 8, 2015 at 5:38 am

    #1810897

    i wasn't sure of your use case, so here's three examples:

    alias = {calculation}

    {calculation}AS Alias

    CalculatedColumn As {calculation}

    SELECT Month1 = convert( varchar(10),GETDATE(),110)

    SELECT convert( varchar(10),GETDATE(),110) AS Month1

    create table #temp (ID int identity(1,1) not null primary key,

    SomeData varchar(30),

    Month1 AS convert( varchar(10),GETDATE(),110) )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 8, 2015 at 5:49 am

    #1810900

    Hi

    Thank you for your response.

    I most probably didn't make myself clear, my apologies.

    It should be something like the following:

    Select 1 AS Month1: 07/07/2015 where the part after the AS keyword now becomes the column name.

    ----------------------|

    Month1: 07/07/2015|

    ----------------------|

    1 |

    ----------------------|

    Kind regards

    Fred

  • July 8, 2015 at 7:03 am

    #1810932

    for that, where the column name is dynamic, i think you have to switch to dynamic SQL, wher eyou build a commadn and execute it instead.

    a rough example:

    DECLARE @cmd varchar(max) = 'Select 1 AS [Month1:' + convert( varchar(10),GETDATE(),110)+']' +',name from sys.tables'

    exec(@cmd)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 8, 2015 at 7:08 am

    #1810933

    Maybe I misunderstand but if you simply want 'Month1: 07/07/2015' as a column name did you try...

    SELECT 1 AS [Month1: 07/07/2015]

    Just a simple example but as you can see the Column name appears as (I understand) you want.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • July 8, 2015 at 7:39 am

    #1810947

    As mentioned above by Lowell you have to achieve the desired result executing dynamic SQL.

  • July 8, 2015 at 7:41 am

    #1810948

    Hi

    No, it should use the getdate function, so that I can advance it to the next month without hard coding the date

  • July 8, 2015 at 7:44 am

    #1810951

    Ahhh...now I understand. Yes, you'll need to use Dynamic SQL for that.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • July 8, 2015 at 8:00 am

    #1810959

    Thanks. I will look into it

  • July 8, 2015 at 9:50 am

    #1811025

    If you're using something else for the front-end, such as SSRS or any other reporting tool, you should format the label on that tool. It's a lot easier to maintain as dynamic sql can be easily abused and get over complicated.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 9, 2015 at 12:15 am

    #1811179

    Thanks Louis, I will see if there is something on SSIS/SSAS that I can use.

  • July 9, 2015 at 8:32 am

    #1811336

    frdrckmitchell7 (7/9/2015)

    Thanks Louis, I will see if there is something on SSIS/SSAS that I can use.

    Using a column that might vary its name from one run to the other is painful for SSIS and SSAS as they both expect static column names. As time passes, you shouldn't add more columns, you should add more rows.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 10, 2015 at 11:41 am

    #1811708

    Luis Cazares (7/8/2015)

    If you're using something else for the front-end, such as SSRS or any other reporting tool, you should format the label on that tool. It's a lot easier to maintain as dynamic sql can be easily abused and get over complicated.

    +1

    Don Simpson


    I'm not sure about Heisenberg.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply