SQL查询优化需求：将00:00时间显示为空字符串

可以通过CASE表达式实现需求，判断格式化后的时间值是否为'00:00'，是则返回空字符串，否则保留原格式化结果。

方法一：直接嵌套CASE判断 #

将你原有的时间格式化逻辑直接放入CASE的判断和返回分支中：

CASE 
    WHEN SUBSTRING(RIGHT('0000' + CAST(RST.DEPOT_DEPARTURE_TIME AS VARCHAR(4)), 4), 1, 2) + ':' + SUBSTRING(RIGHT('0000' + CAST(RST.DEPOT_DEPARTURE_TIME AS VARCHAR(4)), 4), 3, 2) = '00:00'
    THEN ''
    ELSE SUBSTRING(RIGHT('0000' + CAST(RST.DEPOT_DEPARTURE_TIME AS VARCHAR(4)), 4), 1, 2) + ':' + SUBSTRING(RIGHT('0000' + CAST(RST.DEPOT_DEPARTURE_TIME AS VARCHAR(4)), 4), 3, 2)
END AS [Route_Start_Time]

方法二：用CROSS APPLY简化重复计算 #

如果数据量较大，重复计算格式化逻辑会影响性能，可以用CROSS APPLY提前计算一次格式化后的时间，再进行判断：

SELECT 
    CASE WHEN FT.FormattedTime = '00:00' THEN '' ELSE FT.FormattedTime END AS [Route_Start_Time]
FROM 
    YourTableName RST
CROSS APPLY (
    SELECT SUBSTRING(RIGHT('0000' + CAST(RST.DEPOT_DEPARTURE_TIME AS VARCHAR(4)), 4), 1, 2) + ':' + SUBSTRING(RIGHT('0000' + CAST(RST.DEPOT_DEPARTURE_TIME AS VARCHAR(4)), 4), 3, 2) AS FormattedTime
) AS FT

注意：将YourTableName替换为实际的表名

内容的提问来源于stack exchange，提问作者Elvis Ortiz