Styjun

I am trying to make dynamics query using date, I don't know what is wrong

Actual Data of Time Field from SALES Table

Department Time ExtendedPrice
-------------------------------------
Toys 2018-11-01 1205.11
Toys 2018-12-02 1230.27
Baby Care 2018-12-01 6797.95
Sports 2019-01-01 365.8
Sports 2019-01-01 2993.44
Sports 2018-12-02 580.03
Baby Care 2019-01-02 1117.92
Baby Care 2019-01-02 390.75
soap 2018-11-01 3275.25
Baby Care 2018-12-01 367.56

the above date is sample format of actual data

DECLARE @cols AS Varchar(MAX)
select @cols = STUFF((SELECT ',' + convert(varchar(MAX),Time,105)+''
 FROM #Sales
 GROUP by Time
 ORDER by Time

 FOR XML PATH(''), TYPE
 ).value('.', 'VARCHAR(MAX)') 
 ,1,1,'')

Output

select @cols
//output
01-11-2018,02-11-2018,01-12-2018,02-12-2018,01-01-2019,02-01-2019

@query

DECLARE @query AS Varchar(MAX)
SET @query = 'SELECT 
 ' + @cols + '
 FROM 
 (
 SELECT 
 convert(varchar(MAX),Time,105) Time,
 ExtendedPrice
 FROM 
 #Sales
 ) x
 pivot 
 (
 sum(ExtendedPrice)
 for Time in (' + @cols + ')
 ) p '
PRINT @query

it shows error following error

Conversion failed when converting date and/or time from character string.

UPDATE

I tried also this

DECLARE @cols AS Varchar(MAX)
SELECT @cols = STUFF((SELECT ',[' + convert(varchar(MAX),Time,105)+']'
 FROM #Sales
 GROUP by Time
 ORDER by Time
 FOR XML PATH(''), TYPE
 ).value('.', 'VARCHAR(MAX)') 
 ,1,1,'')

 select @cols

//output
[01-11-2018],[02-11-2018],[01-12-2018],[02-12-2018],[01-01-2019],[02-01-2019]

You should Quote the dates as

CREATE TABLE T
 ([Department] varchar(9), [Time] datetime, [ExtendedPrice] int)
;

INSERT INTO T
 ([Department], [Time], [ExtendedPrice])
VALUES
 ('Toys', '2018-11-01 00:00:00', 1205.11),
 ('Toys', '2018-12-02 00:00:00', 1230.27),
 ('Baby Care', '2018-12-01 00:00:00', 6797.95),
 ('Sports', '2019-01-01 00:00:00', 365.8),
 ('Sports', '2019-01-01 00:00:00', 2993.44),
 ('Sports', '2018-12-02 00:00:00', 580.03),
 ('Baby Care', '2019-01-02 00:00:00', 1117.92),
 ('Baby Care', '2019-01-02 00:00:00', 390.75),
 ('soap', '2018-11-01 00:00:00', 3275.25),
 ('Baby Care', '2018-12-01 00:00:00', 367.56)
;

DECLARE @cols AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONVERT(VARCHAR(10), Time, 105))
 FROM T
 GROUP by Time
 ORDER by Time
 FOR XML PATH(''), TYPE
 ).value('.', 'VARCHAR(MAX)') 
 ,1,1,'');

DECLARE @query AS NVARCHAR(MAX)=
 N'SELECT 
 ' + @cols + '
 FROM 
 (
 SELECT 
 convert(varchar(10),Time,105) Time,
 ExtendedPrice
 FROM 
 T
 ) x
 pivot 
 (
 sum(ExtendedPrice)
 for Time in (' + @cols + ')
 ) p ';

EXEC sp_executesql @query;

Returns:

+------------+------------+------------+------------+------------+
| 01-11-2018 | 01-12-2018 | 02-12-2018 | 01-01-2019 | 02-01-2019 |
+------------+------------+------------+------------+------------+
| 4480 | 7164 | 1810 | 3358 | 1507 |
+------------+------------+------------+------------+------------+

Demo