1.创建表
CREATE TABLE [dbo].[DateTimeTest](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[TimeStart] [datetime] NULL,
[TimeEnd] [datetime] NULL
) ON [PRIMARY]
2.插入数据
USE [MyTest]
GO
/****** Object: Table [dbo].[DateTimeTest] Script Date: 11/01/2017 14:37:02 ******/
SET IDENTITY_INSERT [dbo].[DateTimeTest] ON
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (1, CAST(0x0000A81700FF8BEC AS DateTime), CAST(0x0000A817014176EC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (2, CAST(0x0000A81700EF112C AS DateTime), CAST(0x0000A81700FF8BEC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (3, CAST(0x0000A817005AC06C AS DateTime), CAST(0x0000A81700FF8BEC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (4, CAST(0x0000A817005AC06C AS DateTime), CAST(0x0000A81700BDA0EC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (5, CAST(0x0000A817009CAB6C AS DateTime), CAST(0x0000A81700CE1BAC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (6, CAST(0x0000A817014176EC AS DateTime), CAST(0x0000A81701626C6C AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (7, CAST(0x0000A817005AC06C AS DateTime), CAST(0x0000A817007BB5EC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (8, CAST(0x0000A817005AC06C AS DateTime), CAST(0x0000A817014176EC AS DateTime))
INSERT [dbo].[DateTimeTest] ([Id], [TimeStart], [TimeEnd]) VALUES (9, CAST(0x0000A817009CAB6C AS DateTime), CAST(0x0000A81701626C6C AS DateTime))
SET IDENTITY_INSERT [dbo].[DateTimeTest] OFF
3.--获取每一天(每一条数据)08:30-14:30中时间段总和
select
dt.TimeStart,
dt.TimeEnd,
CONVERT(varchar(100), dt.TimeStart, 24)Start,
CONVERT(varchar(100), dt.TimeEnd, 24)[End],
datediff( MINUTE, '08:30:00',CONVERT(varchar(100), dt.TimeStart, 24)) '开始时差',
datediff( MINUTE, '14:30:00',CONVERT(varchar(100), dt.TimeEnd, 24))'结束时差',
case when datediff( MINUTE, '08:30:00',CONVERT(varchar(100), dt.TimeStart, 24))>0 then CONVERT(varchar(100), dt.TimeStart, 24) else '08:30:00' end '交集开始',
case when datediff( MINUTE, '14:30:00',CONVERT(varchar(100), dt.TimeEnd, 24))<=0 then CONVERT(varchar(100), dt.TimeEnd, 24) else '14:30:00' end '交集结束',
case when
(case when datediff( MINUTE, '08:30:00',CONVERT(varchar(100), dt.TimeStart, 24))>0 then CONVERT(varchar(100), dt.TimeStart, 24) else '08:30:00' end)
<
(case when datediff( MINUTE, '14:30:00',CONVERT(varchar(100), dt.TimeEnd, 24))<=0 then CONVERT(varchar(100), dt.TimeEnd, 24) else '14:30:00' end)
then 'true' else 'false' end '是否有交集',
case when
(case when
(case when datediff( MINUTE, '08:30:00',CONVERT(varchar(100), dt.TimeStart, 24))>0 then CONVERT(varchar(100), dt.TimeStart, 24) else '08:30:00' end)
<
(case when datediff( MINUTE, '14:30:00',CONVERT(varchar(100), dt.TimeEnd, 24))<=0 then CONVERT(varchar(100), dt.TimeEnd, 24) else '14:30:00' end)
then 'true' else 'false' end )='true'
then (datediff(MINUTE,
(case when datediff( MINUTE, '08:30:00',CONVERT(varchar(100), dt.TimeStart, 24))>0 then CONVERT(varchar(100), dt.TimeStart, 24) else '08:30:00' end),
(case when datediff( MINUTE, '14:30:00',CONVERT(varchar(100), dt.TimeEnd, 24))<=0 then CONVERT(varchar(100), dt.TimeEnd, 24) else '14:30:00' end)
)
) else 0 end '交集分钟'
from DateTimeTest dt