Why Does Comparing a SQL Date Variable to Null Behave in This Way

Why does comparing a SQL date variable to null behave in this way?

Simply put 'NULL' does not equal 'NULL'. 'NULL' is comparable to a state of uncertainty, where one thing being uncertain does not necessarily equal something else that is also uncertain. Use 'IS NULL', 'ISNULL()', or 'COALESCE()' when testing for nulls. Setting ANSI_NULLS to 'off' can change this behavior, but it is not the ANSI SQL standard. See http://msdn.microsoft.com/en-us/library/ms191270.aspx for more info.

SQL query - dealing with Null Value

Will it work if you treat NULL as Zero (assuming that is valid business logic):

SELECT Name,  ISNULL(Amount,0), ISNULL(Paid,0) as Paid
FROM   Table_1  
WHERE  ISNULL(Paid,0)  <> ISNULL(Amount,0)

How to compare values which may both be null in T-SQL

Use INTERSECT operator.

It's NULL-sensitive and efficient if you have a composite index on all your fields:

IF      EXISTS
        (
        SELECT  MY_FIELD1, MY_FIELD2, MY_FIELD3, MY_FIELD4, MY_FIELD5, MY_FIELD6
        FROM    MY_TABLE
        INTERSECT
        SELECT  @IN_MY_FIELD1, @IN_MY_FIELD2, @IN_MY_FIELD3, @IN_MY_FIELD4, @IN_MY_FIELD5, @IN_MY_FIELD6
        )
BEGIN
        goto on_duplicate
END

Note that if you create a UNIQUE index on your fields, your life will be much simpler.

Where is my banana? (SQL Query NULL issue)

It is because Null is undefined. Null neither equal nor not equal to anything. Please read more on MSDN.

Null values can be checked with is null (or is not null), or isnull() or coalesce() functions depending on the requirement.

Try this:

SELECT * FROM Fruit WHERE Banana is null

And following query to select all the records in case if @FruitInput is null:

SELECT * FROM Fruit WHERE @FruitInput is null or Banana = @FruitInput

SQL Select rows comparing a datetime field that could be null

SELECT * FROM YourTableName 
WHERE active = 1 
AND (active_from  < CURDATE() 
OR active_from IS NULL) 
AND (active_until > CURDATE() 
OR active_until IS NULL);

Why does NULL = NULL evaluate to false in SQL server

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

EDIT:
This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

set ansi_nulls off

if null = null
    print 'true'
else
    print 'false'

set ansi_nulls ON

if null = null
    print 'true'
else
    print 'false'

SQL Server Compare to NULL

I encountered the same problem with you when taking comparison with nullable value, NULL always returns unknown as far away of our desired only between TRUE or FALSE

I ended up with declare a Scalar-valued functions with these logics like other SQL(s) dealing with null as

Ordinary comparison operators yield null (signifying "unknown"), not
true or false, when either input is null. For example, 7 = NULL yields
null, as does 7 <> NULL. When this behavior is not suitable, use the
IS [ NOT ] DISTINCT FROM constructs:

a IS DISTINCT FROM b => a != b
a IS NOT DISTINCT FROM b => a == b

Which a IS NOT DISTINCT FROM b could be rewritten as

(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)

I use sql_variant for these basic parameters: int, datetime, varchar,...

create function IsEqual(
    @a sql_variant,
    @b sql_variant
)
returns bit
as
begin
    return (CASE WHEN (@a IS NOT NULL AND @b IS NOT NULL AND @a=@b) OR (@a IS NULL AND @b is NULL) THEN 1 ELSE 0 END);
end

create function IsNotEqual(
    @a sql_variant,
    @b sql_variant
)
returns bit
as
begin
    return 1-dbo.IsEqual(@a,@b);
end

To use

select dbo.IsEqual(null, null) Null_IsEqual_Null,
dbo.IsEqual(null, 1) Null_IsEqual_1,
dbo.IsEqual(1, null) _1_IsEqual_Null,
dbo.IsEqual(1, 1)  _1_IsEqual_1,
dbo.IsEqual(CAST('2017-08-25' AS datetime), null) Date_IsEqual_Null,
dbo.IsEqual(CAST('2017-08-25' AS datetime), CAST('2017-08-25' AS datetime)) Date_IsEqual_Date

For your cases

select dbo.IsNotEqual(123,123) _123_IsNotEqual_123,
dbo.IsNotEqual(5,123) _5_IsNotEqual_123,
dbo.IsNotEqual(Null,123) Null_IsNotEqual_123,
dbo.IsNotEqual(123,Null) _123_IsNotEqual_Null,
dbo.IsNotEqual(Null,Null) Null_IsNotEqual_Null

Is it necessary to test for NULL if also testing for greater than?

Compare with NULL is necessary if you use ELSE statements:

for example:

declare @t int
set @t=null
if (@t>0) print '1' -- works fine
if (@t<0) print '2' --works fine

if (@t>0) 
    print '3' --works fine
else print '4' --here we start getting problems, because we are sure that @t<=0 that is obviously not true

Unexpected result when comparing dates

MySQLs implicit type conversion can be very surprising. If you want to understand the behavior of your queries, you can try to apply the type conversion rules as described in Type Conversion in Expression Evaluation. However - I failed to do that for your case. For example: For the two expressions date > '2019' and date > 2019 I would apply the following rule:

If one of the arguments is a TIMESTAMP or DATETIME column and the
other argument is a constant, the constant is converted to a timestamp
before the comparison is performed.

But that cannot be the case, because neither the number 2019 nor the string '2019' can be converted to a temporal type. Here is a query, which demonstrates some implicit conversions:

select '2019' + interval 0 day -- implicit cast to date(time)
     , 2019 + interval 0 day
     , 20190101 + interval 0 day
     , 190101 + interval 0 day
     , '2019*01*01' + interval 0 day
     , '2019-01-01' + interval 0 day
     , '2019-01-01' + 0 -- implicit cast to numeric
     , date('2019-01-01') + 0
     , date('2018-01-01') > 2019
     , date('2018-01-01') > '2019'
;

Result:

Expression                    | Result
------------------------------|-----------
'2019' + interval 0 day       | null
2019 + interval 0 day         | null
20190101 + interval 0 day     | 2019-01-01
190101 + interval 0 day       | 2019-01-01
'2019*01*01' + interval 0 day | 2019-01-01
'2019-01-01' + interval 0 day | 2019-01-01
'2019-01-01' + 0              | 2019
date('2019-01-01') + 0        | 20190101
date('2018-01-01') > 2019     | 1
date('2018-01-01') > '2019'   | 0

As you see, when we try to convert 2019 or '2019' to a date (or datetime), we get NULL. Thus the conditions should also be evaluated to NULL and the result set would be empty. But as we know, that is not the case. Maybe I'm just wrong, assuming that 2019 and '2019' are constants. But then I don't know what they could mean.

So I can only make assumptions. And my assumtion is: Whenever one comparator is numeric, the other value is also converted to a numeric value. This would be the case for date > 2019 aswell as for date > year(@THIS_YEAR). In this case the date 2018-01-01 is converted to 20180101 (see the table above), which (in numeric context) is greater than 2019. So you get rows from the year 2018.

For date > '2019' I can only assume, that the values are compared as strings. And '2018-01-01' as string is considered "smaller" than 2019.

But even if that behavior would be properly documented, the rules are too difficult to remember, because one can hardly see any logic behind them. (I don't say - there is no logic - I just don't see any.)

So I can give you one advise: If you want to compare two incompatible types, always cast or convert them to be compatible.

WHERE year(date) >= year(@THIS_YEAR)

would be fine, since you compare two numeric values. But that is not necessery in your case and you can just use

WHERE date >= @THIS_YEAR

because 2019-01-01 00:00:00 in

`SET @THIS_YEAR = "2019-01-01 00:00:00";`

is a perfectly formatted DATETIME string and can be considered compatible with the DATETIME type. '2019-01-01' would be just fine aswell.

Note that if you wrapp a column into a function call (like year(date)) you will loose the ability to use an index on that column.

---

Related Topics