MASTER.dbo.spt_values 应用——按半小时分组

DECLARE @t TABLE (
	d DATETIME
)
INSERT INTO @t (d) VALUES( '2016-08-21 18:13' )
INSERT INTO @t (d) VALUES( '2016-08-21 18:51' )

;WITH t AS (
	SELECT number,CONVERT(CHAR(8), DATEADD(n, (number-1)*30, CONVERT(CHAR(10), GETDATE(),120)),108) AS beginTimeSeg
	, CONVERT(CHAR(8), DATEADD(n, (number)*30, CONVERT(CHAR(10), GETDATE(),120)),108) AS endTimeSeg
	FROM MASTER.dbo.spt_values AS SV WHERE SV.type='P' AND SV.number BETWEEN 1 AND 48
)
,t2 AS (
	SELECT *, (SELECT t.number FROM t WHERE CONVERT(CHAR(8),a.d,108) >=t.beginTimeSeg AND CONVERT(CHAR(8),a.d,108)<t.endTimeSeg) AS seg FROM @t A
)
SELECT a.d,a.seg,b.beginTimeSeg,b.endTimeSeg FROM t2 A INNER JOIN t B ON a.seg=b.number
/*
d	                    seg	beginTimeSeg  endTimeSeg
2016-08-21 18:13:00.000	37	18:00:00	    18:30:00
2016-08-21 18:51:00.000	38	18:30:00	    19:00:00 
*/