如何把一个时间段拆分成每一天

baidu_35821964 2016-09-06 05:47:59

如图所示我想把它拆分成类似于

14号的就取 begindate 12;00 enddate 17;30
15,16 就取 begindate 9;00 enddate 17;30
17号取 begindate 9;00 enddate 22:00

其他字段不变
应该怎么样写帮个忙谢谢

...全文

2250 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

中国风 2016-09-10

打赏
举报


;WITH CTED(begindate,enddate,duration)
AS
(
SELECT '2016-04-14 12:00','2016-04-17 22:00',3
)
SELECT  
	begindate=CASE WHEN b.number=0 THEN a.begindate ELSE CONVERT(VARCHAR(10),DATEADD(dd,b.number,a.begindate),120)+' 09:00' END
	,enddate=CASE WHEN a.duration=b.number THEN a.enddate ELSE CONVERT(VARCHAR(10),DATEADD(dd,b.number,a.begindate),120)+' 22:00' END
	,a.duration
	,a.*
FROM    CTED AS a
        INNER JOIN master.dbo.spt_values AS b ON b.type = 'P'
                                                 AND b.number >= 0
                                                 AND b.number <= a.duration;
/*
begindate	enddate	duration
2016-04-14 12:00	2016-04-14 22:00	3
2016-04-15 09:00	2016-04-15 22:00	3
2016-04-16 09:00	2016-04-16 22:00	3
2016-04-17 09:00	2016-04-17 22:00	3
*/

Net攻城狮 2016-09-10

打赏
举报

sql 日期格式化问题 Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06 Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16 Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06 Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06 Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06 Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06 Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06 Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46 Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06 Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16 Select CONVERT(varchar(100), GETDATE(), 12): 060516 Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937 Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967 Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47 Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157 Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16 Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47 Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250 Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006 Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16 Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006 Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006 Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006 Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006 Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006 Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49 Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006 Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16 Select CONVERT(varchar(100), GETDATE(), 112): 20060516 Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513 Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547 Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49 Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700 Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827 Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

道素 2016-09-10

打赏
举报

引用楼主 baidu_35821964 的回复:

如图所示我想把它拆分成类似于 14号的就取 begindate 12;00 enddate 17;30 15,16 就取 begindate 9;00 enddate 17;30 17号取 begindate 9;00 enddate 22:00 其他字段不变应该怎么样写帮个忙谢谢

没理解你的意思是，你是想将12行一行变成三行吗（跨3天）然后每天默认起始时间为9：00，结束时间为17：30 下面是我的理解，有一种情况我没处理，比如开始时间就超过17：30如何处理？


;WITH tb(ID,BeginDate,EndDate) AS (
	SELECT 12,CONVERT(DATETIME,'2016-04-14 12:00:00'),CONVERT(DATETIME,'2016-04-17 22:00:00') UNION
	SELECT 13,'2016-04-12 17:27:00','2016-04-13 17:27:00'
)
SELECT tb.ID
      ,CASE WHEN sv.number=1 THEN 
          CASE WHEN  DATEADD(d,sv.number-1,BeginDate)<DATEADD(hh,9, CONVERT(VARCHAR,DATEADD(d,sv.number-1,BeginDate),23)) THEN DATEADD(hh,9, CONVERT(VARCHAR,DATEADD(d,sv.number-1,BeginDate),23)) ELSE DATEADD(d,sv.number-1,BeginDate) END
       ELSE DATEADD(hh,9, CONVERT(VARCHAR,DATEADD(d,sv.number-1,BeginDate),23)) END
      ,CASE WHEN DATEDIFF(d, DATEADD(d,sv.number-1,BeginDate),EndDate)=0 THEN
      	 CASE WHEN EndDate >(CONVERT(VARCHAR,EndDate,23)+' 17:30:00') THEN EndDate ELSE CONVERT(VARCHAR,EndDate,23)+' 17:30:00' END
      ELSE CONVERT(VARCHAR,DATEADD(d,sv.number-1,BeginDate),23)+' 17:30:00' END
FROM tb
CROSS APPLY [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND DATEDIFF(d,tb.BeginDate,tb.EndDate) 

/*
ID	(No column name)	(No column name)
12	2016-04-14 12:00:00.000	2016-04-14 17:30:00.000
12	2016-04-15 09:00:00.000	2016-04-15 17:30:00.000
12	2016-04-16 09:00:00.000	2016-04-16 17:30:00.000
13	2016-04-12 17:27:00.000	2016-04-12 17:30:00.000
*/