Collaps function on Date from SQL, failing to give result

Question

I am trying to use collapse on date, but the query shown below is giving ;ast value of table followed with comma with last value itself.

Same if I apply on Varchar value it works fine.

DECLARE @Names VARCHAR(8000)  

SELECT @Names = COALESCE(CONVERT(VARCHAR(100),CONVERT(DATE,t1.StartDateTime,103)) + ', ', '') 
                + CONVERT(VARCHAR(100),CONVERT(DATE,t1.StartDateTime,103)) 
FROM   tabletest t1 
where t1.StartDateTime BETWEEN CONVERT(DATE,@StartDate,103) AND CONVERT(DATE,@Enddate,103)
SELECT @Names

I have column values like this

+----------
|11-12-2015 |
|12-12-2015 | 
|13-12-2015 |
|14-12-2015 |
|15-12-2015 |
|16-12-2015 |
|___________|

I want output as ...

(11-12-2015,12-12-2015,13-12-2015,14-12-2015,15-12-2015,16-12-2015)

Please provide sample data and desired results. SQL Server does not guarantee the values assigned to variables in a `select` statement that has multiple rows. — Gordon Linoff, Dec 21 '15 at 12:29
Use XML PATH to concatenate strings: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Alex Yu, Dec 21 '15 at 12:44

score 0 · Accepted Answer · answered Dec 21 '15 at 13:40

0

Following code have solved my problem

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Isnull(CAST (CONVERT(DATE,SP.StartDateTime,103) as Varchar),'')
FROM testtable AS SP
SELECT @listStr
GO

answered Dec 21 '15 at 13:40

Pushkar Phule

160
11

Collaps function on Date from SQL, failing to give result

1 Answers1