SQL Server-从DateTime获取不同的时机

Question

我需要根据不同条件获得时间部分。

我到目前为止写的代码如下：

CREATE TABLE TimeTable 
(
   EarliestStart datetime null,
   LatestEnd datetime null
);
INSERT INTO TimeTable select '2017-08-28 10:00:00.000', '2017-08-28 12:00:00.000' union all --expected: 10-12
select '2017-08-29 10:15:00.000', '2017-08-28 12:00:00.000' union all --expected: 10.15-12
select '2017-08-28 10:00:00.000', '2017-08-28 12:15:00.000' union all --expected 10-12.15
select '2017-08-28 00:00:00.000', '2017-08-28 23:59:00.000' --expected empty
insert into TimeTable (EarliestStart) values('2017-08-28 16:59:00.000') --expected 16:59-
insert into TimeTable (LatestEnd) values('2017-08-28 16:59:00.000') --expected -16:59
select 
    case 
        when earlieststart is null and latestend is null then ''
        when CONVERT(NCHAR(5),earlieststart,108) = '00:00' and CONVERT(NCHAR(5),latestend,108) = '23:59' then ''
    else
        case when earlieststart is null then '' 
             when RIGHT(CONVERT(NCHAR(5),earlieststart,108),2) = '00' then CONVERT(NCHAR(2),earlieststart,108) else CONVERT(NCHAR(5),earlieststart,108) end 
        + '-' + 
        case when latestend is null then '' 
             when RIGHT(CONVERT(NCHAR(5),latestend,108),2) = '00' then CONVERT(NCHAR(2),latestend,108) else CONVERT(NCHAR(5),latestend,108) end
end
from TimeTable

有什么方法可以更好地做到这一点？我正在使用SQL Server2008。

小提琴

Answer 1

我不知道它的性能是否更好，但是查询只有一个案例语句：

SELECT CASE WHEN earlieststart is null AND latestend is null 
                OR CONVERT(CHAR(5),earlieststart,108) = '00:00' AND CONVERT(CHAR(5),latestend,108) = '23:59' 
       THEN '' 
        ELSE COALESCE(REPLACE(CONVERT(CHAR(5),EarliestStart,108),':00', '') ,'') +'-'+ COALESCE(REPLACE(CONVERT(CHAR(5),LatestEnd,108),':00', '') ,'') 
        END AS L1    
FROM TimeTable