Quotename Error

ojmp2001 ojmp2001 121 Reputation points
2023-02-07T22:23:35.13+00:00

I have the following statement and am getting an error near keyword 'end'

Declare @Col Nvarchar(Max)= ''
Select @Col +=Quotename(CAST(Datepart(Year, CAST([STARTDATE] AS DATE)) AS NVARCHAR(10)) + ' - Q' + 
(CAST(Datepart(Quarter, CAST([STARTDATE] AS DATE)) AS NVARCHAR(10)) END + ',' FROM MyTable
SET @Col = LEFT(@Col, LEN(@Col) - 1);
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2023-02-07T22:36:31.91+00:00

    Try the following:

    DECLARE @t TABLE (StartDate DATETIME)
    INSERT INTO @t VALUES (CURRENT_TIMESTAMP), (DATEADD(QUARTER, -2, CURRENT_TIMESTAMP))
    DECLARE @Col Nvarchar(Max)= ''
    Select @Col +=Quotename(CAST(Datepart(Year, CAST([STARTDATE] AS DATE)) AS NVARCHAR(10)) + ' - Q' + 
    CAST(Datepart(Quarter, CAST([STARTDATE] AS DATE)) AS NVARCHAR(10)))  + ',' FROM @t;
    SET @Col = LEFT(@Col, NULLIF(LEN(@Col),0) - 1);
    SELECT @col
    
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
    2023-02-08T02:13:43.27+00:00

    You can try string_agg as another option:

    Select  @col=
    string_agg(Quotename(CAST(Datepart(Year, CAST([STARTDATE] AS DATE)) AS NVARCHAR(10)) 
    + ' - Q' +CAST(Datepart(Quarter, CAST([STARTDATE] AS DATE)) AS NVARCHAR(10))) , ',') 
    within group (order by  [STARTDATE])  
    FROM (select distinct [STARTDATE] from @t) t
     
    SELECT @col;
    
    0 comments No comments