Create a string of dates in single field

Transact-SQL
1

I need to take a list of dates (ie):

06/01/2015
06/02/2015
06/03/2015
06/10/2015

And put them into a single field displayed like this:
06/01-06/03, 06/10

Is this possible?

2

You can do the following:

SELECT ',' + YourDateColumnFormattedTheWayYouwant
FROM YourTable
FOR XML PATH('');

That will give you the string you want, but it will have a comma as the first character. To strip that out,

SELECT STUFF
((
	SELECT ',' + YourDateColumnFormattedTheWayYouwant
	FROM YourTable
	FOR XML PATH('')
),1,1,'');

You can format the your date column the way you want using CAST or CONVERT. For example, to get the MM/DD/YYYY format, use CONVERT(VARCHAR(32), YourDateColumn, 101)

This does not of course, take into account aggregating adjacent dates into a range. I saw that that is what you wanted only after I posted this. That can be done as well, but it takes a bit more work and time. If you look up islands and gaps in SQL they discuss ways to do that.

1 Like
3

I am still struggling getting the dates output the way I want. I just can't seem to either wrap my head around it - or get the sql right. I've read all the gaps and islands on dates and have something that will give me some data - but still not usable. Someone mentioned a loop - but - I'm fighting with that too. Any direction would be great! TIA!!

4

Here is some code that you can copy to SSMS query window and run.

-- create a table with sample data.
CREATE TABLE #tmp(dt DATE);

INSERT INTO #tmp VALUES 
('20150601'),('20150602'),('20150603'),('20150610'),
('20150801'),('20150902'),('20150903'),('20150904')

-- create a numbers table if you don't already have one.
CREATE TABLE #N (N INT PRIMARY KEY );
INSERT INTO #N 
SELECT TOP 1000 
	ROW_NUMBER() OVER (ORDER BY a.number,b.number)-1
FROM 
	master..spt_values a CROSS JOIN master..spt_values b

-- This is the min and max date within which you want to 
-- process the data.
DECLARE @startDate DATE = '20150601',
	@endDate DATE = '20150930';

-- This is the query
-- the cte creates a group number of contiguous dates (islands)
;WITH cte AS
(
	SELECT *, 
		N-ROW_NUMBER() OVER (ORDER BY dt) Grp
	FROM 
		#N a
		LEFT JOIN #tmp t ON
			DATEADD(dd,n,@startDate) = t.Dt
	WHERE
		DATEADD(dd,n,@startDate) <= @endDate
		AND Dt IS NOT NULL
)
SELECT -- use the group number to get the results.
	CASE 
		WHEN COUNT(Grp) = 1 THEN 
			MAX(CONVERT(VARCHAR(32),dt,101))
		ELSE
			CONVERT(VARCHAR(32),MIN(dt),101) + '-' + CONVERT(VARCHAR(32),MAX(dt),101)
		END
FROM
	cte
GROUP BY
	Grp;
	
DROP TABLE #tmp;
DROP TABLE #N;