Sql服务器 - 从日期时间获取不同的时间部分

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 Server 2008

Fiddle

Answer 1

我不知道是否有更好的表现，但下面的查询只有一个CASE语句：

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