strange results in string_ag() function

Question

hi guys
i have updated the sql server and now I get the result for below query
select LBrCode,PrdAcctId,string_agg(DpDate,' ,')
--within group (order by DpDate ) all_dates
from D009046 where LBrCode=1 and PrdAcctId='CC 000000000000000800000000'
-- where DpDate<='31-mar-2019'
group by LBrCode,PrdAcctId,DpDate( It Should be concatenated)
result

1   CC      000000000000000800000000    Apr  1 2013 12:00AM
1   CC      000000000000000800000000    Apr  2 2013 12:00AM
1   CC      000000000000000800000000    Jul  2 2013 12:00AM
1   CC      000000000000000800000000    Oct  2 2013 12:00AM
1   CC      000000000000000800000000    Nov 30 2013 12:00AM
1   CC      000000000000000800000000    Feb 28 2014 12:00AM
1   CC      000000000000000800000000    May 30 2014 12:00AM
1   CC      000000000000000800000000    Aug 30 2014 12:00AM
1   CC      000000000000000800000000    Dec  1 2014 12:00AM
1   CC      000000000000000800000000    Mar  1 2015 12:00AM
1   CC      000000000000000800000000    Jun  1 2015 12:00AM
1   CC      000000000000000800000000    Sep  1 2015 12:00AM
1   CC      000000000000000800000000    Dec  1 2015 12:00AM
1   CC      000000000000000800000000    Mar  1 2016 12:00AM
1   CC      000000000000000800000000    Jun  1 2016 12:00AM
1   CC      000000000000000800000000    Sep  1 2016 12:00AM
1   CC      000000000000000800000000    Dec  1 2016 12:00AM
1   CC      000000000000000800000000    Mar  1 2017 12:00AM
1   CC      000000000000000800000000    Jun  1 2017 12:00AM
1   CC      000000000000000800000000    Sep  1 2017 12:00AM
1   CC      000000000000000800000000    Dec  1 2017 12:00AM
1   CC      000000000000000800000000    Mar  1 2018 12:00AM
1   CC      000000000000000800000000    Jun  1 2018 12:00AM
1   CC      000000000000000800000000    Sep  1 2018 12:00AM
1   CC      000000000000000800000000    Dec  1 2018 12:00AM
1   CC      000000000000000800000000    Mar  1 2019 12:00AM

However when i uncomment within group it gives me error
query I am using is enter code here

select LBrCode,PrdAcctId,string_agg(DpDate,'  ,') 
--within group (order by DpDate ) all_dates 
from D009046 where LBrCode=1 and PrdAcctId='CC      000000000000000800000000'
 -- where DpDate<='31-mar-2019'
group by LBrCode,PrdAcctId,DpDate

result is

Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '('.

PLease help ....

Answer 1

Didn't you say that the problem was solved in your other thread: https://learn.microsoft.com/en-us/answers/questions/687881/string-agg-not-working-properly.html?childToView=688226#answer-688226

Anyway, check the compatibility level of this database:

   SELECT compatibility_level FROM sys.databases WHERE name = db_name()  

I found after some experimentation that the compat level must be at least 110 for WITHIN GROUP to be accepted. (Which is a little odd, since string_agg was introduced in SQL 2017, and 110 is SQL 2012.)

You can set the compatibility level to the level of SQL 2017 with this command:

   ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140  

Note that there is small risk that other things will break when you do this.