时间戳

首页 > 解决方案 > 如何在sql中指示日期为空

sql - 如何在sql中指示日期为空

问题描述

使用以下代码，我希望检索 ClosedDateTimeT 字段具有空值的记录，但它仅显示具有值的记录。我在这里做错了吗：

SELECT DISTINCT
    t.ticketid,
    t.Sub,
    i.status, 
    i.OwnedByTeam,
    i.OwnedByTeamT,
    i.TypeOptions,
    i.ClosedDateTimeT,
    i.CompletedDateTime,
    i.TitleT 
FROM
    ticket AS t 
INNER JOIN
    Incident AS i ON t.TicketID = i.IncidentID
WHERE
    CAST(t.Sub AS DATE) >= '01-01-2019' 
    AND CAST(t.Sub AS DATE ) <= '05-30-2019'
    AND i.Subcategory = 'Content Request'
    AND (i.TypeOptions = 'New/Initial Submission' OR
         i.TypeOptions = 'Am/Re')
    AND ((i.OwnedByTeamT LIKE '%C - CA%' 
          AND i.TitleT LIKE '%B Build%' 
          AND i.ClosedDateTimeT IS NOT NULL) OR
         (i.OwnedByTeamT LIKE '%C - CA%'
          AND i.TitleT LIKE '%B Build%'
          AND i.ClosedDateTimeT IS NULL) OR
         (i.OwnedByTeamT LIKE '%E - Cl%' 
          AND i.TitleT LIKE '%OP B V%'))

标签： sqlsql-server

解决方案

不太确定您面临的问题是什么，但您的查询可以稍微简化并格式化。请注意，我也在使用符合 ANSI 的日期文字。

Select distinct t.ticketid
    , t.Sub
    , i.status
    , i.OwnedByTeam
    , i.OwnedByTeamT
    , i.TypeOptions
    , i.ClosedDateTimeT
    , i.CompletedDateTime
    , i.TitleT 
From ticket as t 
inner join Incident as i on t.TicketID=i.IncidentID
where 
    CAST(t.Sub AS DATE) >= '20190101' 
    AND CAST(t.Sub AS DATE ) <= '20190530'
    AND i.Subcategory = 'Content Request'
    AND i.TypeOptions in ('New/Initial Submission', 'Am/Re')
    AND 
    (
        (
            i.OwnedByTeamT like '%C - CA%' 
            AND 
            i.TitleT LIKE '%B Build%'
        )
        OR
        (
            i.OwnedByTeamT like '%E - Cl%' 
            AND 
            i.TitleT LIKE '%OP B V%'
        )
    )

推荐阅读