title: Why does comparing a SQL date variable to null behave in this way?
body: <p>I just came across an interesting problem with a procedure I am writing in SQL.</p>
  
  <p>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.</p>
  
  <p>I did a fairly basic <code>IF(@dateVariable &lt;&gt; NULL AND @DateVariable2 &lt;&gt; NULL)</code> 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.</p>
  
  <p>To get around this I just did <code>IF(DateVariable IS NOT NULL)</code> which works correctly. I also tried <code>IF( ISNULL(@DateVariable,'') &lt;&gt; '')</code> which also works correctly</p>
  
  <p>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?</p>
  
  <p>Example:</p>
  
  <p>----- Fails -----</p>
  
  <pre><code>DECLARE @Date DATETIME
  SET @Date = CURRENT_TIMESTAMP
  
  IF (@Date &lt;&gt; NULL)
  BEGIN
      print('a')
  END
  </code></pre>
  
  <p>----- Works -----</p>
  
  <pre><code>DECLARE @Date DATETIME
  SET @Date = CURRENT_TIMESTAMP
  
  IF (ISNULL(@Date,'') &lt;&gt; '')
  BEGIN
      print('a')
  END
  
  DECLARE @Date DATETIME
  SET @Date = CURRENT_TIMESTAMP
  
  IF (@Date IS NOT NULL)
  BEGIN
      print('a')
  END
  </code></pre>