CODEWITHSUNDEEP
sqlsql-server
eikaz
eikaz

Reputation: 401

SQL Server : CASE in WHERE clause

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:

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

Upvotes: 0

Views: 76

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 3

Eric
Eric

Reputation: 5733

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

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

Upvotes: 1

Related Questions