SQL Server：WHERE子句中的CASE

I need to create a SQL statement to check where condition if the column is null, it will do other condition. Below is my example:

我需要创建一个SQL语句来检查列为空的条件，它将执行其他条件。以下是我的例子：

SELECT  
    REPLACE(xxShip.Shipment_Date, '/', '-') AS ShipDate,xxShip.Customer,
    xxShip.TravelSheetNo
FROM 
    (SELECT 
         RANK() OVER (PARTITION BY TravelSheetNo ORDER BY created_on DESC) AS shipwocode,
         ShipDate, ShipTime, Shipment_Date, 
         Customer, Product_no, TravelSheetNo,
         [status], ProcessLotNo
     FROM  
         xxDEShipment) xxShip
WHERE 
    xxShip.shipwocode = 1
    AND xxShip.TravelSheetNo = 'ABC'
    --here i need to check the condition----------------------------
    AND (CASE 
            WHEN ISNULL(xxShip.Shipment_Date,'') != '' OR xxShip.Shipment_Date != '' 
              THEN
                 xxShip.Shipment_Date IS NULL OR xxShip.Shipment_Date = '' 
              ELSE
                 CONVERT(DATETIME, xxShip.Shipment_Date) >=  @DATESTART and CONVERT(DATETIME, xxShip.Shipment_Date) <=  @DATEEND
         END)

Please help. Thank you

请帮忙。谢谢

2 个解决方案

#1

This should be something like this:

这应该是这样的：

AND(
     (ISNULL(xxShip.Shipment_Date,'') <> '' AND 
      CONVERT(DATETIME, xxShip.Shipment_Date) >=  @DATESTART AND 
      CONVERT(DATETIME, xxShip.Shipment_Date) <=  @DATEEND
     ) 
     OR ISNULL(xxShip.Shipment_Date,'') == ''
   )

Note that storing dates as strings is a bad practice.

请注意，将日期存储为字符串是一种不好的做法。

#2

If you need to select dates between @DATESTART and @DATEEND or null or empty date, use below:

如果您需要选择@DATESTART和@DATEEND之间的日期或null或空日期，请使用以下内容：

ISNULL(CONVERT(DATETIME, NULLIF(xxShip.Shipment_Date, '')), @DATESTART) 
    BETWEEN @DATESTART AND @DATEEND

#1