Pushkar Phule Pushkar Phule - 4 years ago 119
SQL Question

Collaps function on Date from SQL, failing to give result

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)

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download