0

I have the following query :

DECLARE @dt AS DATE = NULL;

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE shippeddate = @dt;

I have some Orders with NULL shippeddate but they are not returned when executing the above query.

mohamed-mhiri
  • 202
  • 3
  • 22

3 Answers3

3

You need to check for NULL using IS NULL. So:

WHERE shippeddate = @dt OR (shippeddate IS NULL AND @dt IS NULL)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
DECLARE @dt AS DATE = NULL;

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE shippeddate = @dt  or (shippeddate IS NULL AND @dt IS NULL)
Vahid Heydarinezhad
  • 116
  • 4
-1

If you set ANSI_NULLS to OFF you will get what you want to get but it is not recommended : 

SET ANSI_NULLS OFF;

In this case predicate that evaluates to UNKNOWN. Obviously, NOT TRUE is FALSE and NOT FALSE is TRUE. However, NOT UNKNOWN remains UNKNOWN. In another way :

NULL has no value, and so cannot be compared using the scalar value operators.

You have to use ISNULL() .

DECLARE @dt AS DATE = NULL;

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE ISNULL(shippeddate, '99991231') = ISNULL(@dt, '99991231')
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • There is no need to use a _magic value_ in solving this problem, i.e. `99991231`, and they are generally a bad habit. (If another clever programmer decided to use the same value as a flag to indicate that an order is "on hold" then things may get interesting.) Using [`is null`](https://learn.microsoft.com/en-us/sql/t-sql/queries/is-null-transact-sql?view=sql-server-ver15) avoids the issue entirely. – HABO Apr 05 '20 at 21:01