2015-01-01 2015-01-17 2015-02-17 2015-05-30 2015-06-01
我正在SQL中查找事件的结束日期。以下是我所掌握的数据示例。
EventDate EventStartTime EventEndTime
01/01/2015 14:17 16:50
04/01/2015 19:20 22:00
17/01/2015 22:30 03:35
17/02/2015 22:50 03:35
30/05/2015 23:30 01:50
01/06/2015 11:40 14:50
我想要的输出:
EventDate EventStartTime EventEndTime EventEndDate
01/01/2015 14:17 16:50 01/01/2015
04/01/2015 19:20 22:00 04/01/2015
17/01/2015 22:30 03:35 18/01/2015
17/02/2015 22:50 03:35 18/02/2015
30/05/2015 23:30 01:50 31/05/2015
01/06/2015 11:40 14:50 01/06/2015
如果有人知道如何编码,我会非常感激。
只有我能从你的问题中得出,如果END DATE
低于START DAY
,那就是第二天。所以我的问题是:
SELECT
EventDate,
EventStartTime,
EventEndTime,
CASE
WHEN EventEndTime < EventStartTime THEN DATEADD(DAY, 1, EventDate) ELSE EventDate
END AS EventEndDate
FROM
Events
select EventDate ,EventStartTime,EventEndTime
,(
case when cast(EventStartTime as date)> cast(EventEndTime as date)
then cast(cast(EventDate as datetime) +1 as date)
else cast(EventDate as date)
end
) EventEndDate
from TableName
试试这个,如果我理解你的话,它会起作用的。
您可以通过case when
表达式轻松实现。如果EventEndDate小于EventStartDate,则添加一天的EventDate,如果EventEndDate大于EventStartDate则选择EventDate作为EventStartDate(根据您的输出(。
模式和插入语句:
create table events(EventDate date, EventStartTime time, EventEndTime time);
insert into events values('2015-01-01', '14:17', '16:50');
insert into events values('2015-01-04', '19:20', '22:00');
insert into events values('2015-01-17', '22:30', '03:35');
insert into events values('2015-02-17', '22:50', '03:35');
insert into events values('2015-05-30', '23:30', '01:50');
insert into events values('2015-06-01', '11:40', '14:50');
查询:
select EventDate,EventStartTime,EventEndTime,
(case when EventEndTime>=EventStartTime then EventDate else dateadd(day,1,EventDate) end)EventStartDate
from events
输出:
事件开始时间