为什么将 SQL 日期变量与 null 进行比较会有这种行为?

问题描述

我刚刚在用 SQL 编写的过程中遇到了一个有趣的问题.

I just came across an interesting problem with a procedure I am writing in SQL.

在我的 proc 中，我有 2 个日期，它们是默认为 NULL 的可选参数，我想检查这些参数是否不为 null，如果不运行我的 proc 的一部分，如果它们为 null，则 proc 的额外部分是忽略.

In my proc I have 2 dates, which are optional params defaulted to NULL, I want to check if these params are not null and if not run part of my proc, if they are null then the extra part of the proc is ignored.

我做了一个相当基本的 IF(@dateVariable <> NULL AND @DateVariable2 <> NULL) 语句，但是即使变量不为空，if 语句也永远不会工作，我会假设 SQL 正在努力将日期与 NULL 进行比较，这很奇怪，因为 datetime 可以为空.

I did a fairly basic IF(@dateVariable <> NULL AND @DateVariable2 <> NULL) statement, but the if statement never works even if the variables are not null, I would assume SQL is struggling to compare the date to a NULL which is strange since datetime is nullable.

为了解决这个问题，我只是做了 IF(DateVariable IS NOT NULL) ，它可以正常工作.我也试过 IF( ISNULL(@DateVariable,'') <> '') 也能正常工作

To get around this I just did IF(DateVariable IS NOT NULL) which works correctly. I also tried IF( ISNULL(@DateVariable,'') <> '') which also works correctly

所以我的问题是为什么第一个 IF 不起作用，但第二个和第三个 IF 都起作用，因为两者都必须在某个时候将变量的内容与 null 进行比较?

So my question is why does the first IF not work, but the second and third IF both do since both must at some point compare the contents of the variable to null?

示例:

----- 失败 -----

----- Fails -----

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (@Date <> NULL)
BEGIN
    print('a')
END

----- 作品-----

----- Works -----

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (ISNULL(@Date,'') <> '')
BEGIN
    print('a')
END

DECLARE @Date DATETIME
SET @Date = CURRENT_TIMESTAMP

IF (@Date IS NOT NULL)
BEGIN
    print('a')
END

推荐答案

简单地说，'NULL' 不等于 'NULL'.NULL"相当于一种不确定状态，其中不确定的事物不一定等于其他事物也不确定.测试空值时使用IS NULL"、ISNULL()"或COALESCE()".将 ANSI_NULLS 设置为 'off' 可以更改此行为，但这不是 ANSI SQL 标准.请参阅 http://msdn.microsoft.com/en-us/library/ms191270.aspx 了解更多信息.

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 日期变量与 null 进行比较会有这种行为?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！