Format Date column in dynamic pivot query in SQL Server

You need to pass single quote value in string as expected for @DynamicPivoteQuery value if it’s not pass correctly it will not execute the @DynamicPivoteQuery as there are syntax error problem.

Refer the below test query and impliment it as per your code logic.

SQL

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @StartDate  DATETIME  
 
SELECT @ColumnName=  ISNULL(@ColumnName + ',','') 
       + QUOTENAME(CatName)
FROM (SELECT DISTINCT CatName FROM tblCategory) AS BCats 


SET @DynamicPivotQuery = 
'SELECT       REPLACE(CONVERT(CHAR(15), RcvdDate, 106),'' '','' - '')  AS RcvdDate, ' + @ColumnName + 
' from
(
SELECT       RcvdDate, CatName, Amount
FROM         tblTransaction INNER JOIN
                      tblCategory ON #tblCategory.CatNo = tblTransaction.BCatId
) x
PIVOT 
    (
        SUM(x.Amount) for x.CatName in (' + @ColumnName + ')
    ) P'
 
EXEC(@DynamicPivotQuery);