Wednesday, July 21, 2010

Why null sucks. Reason 641.

A little bit of sql code here:

DECLARE @test DATETIME

SET @test = NULL

IF (@test <> '7/31/2010')
SELECT '@test is not equal to 7/31/2010'
ELSE SELECT '@test is equal to 7/31/2010'

Amazing. We've just figured out that a null date is equal to 7/31/2010. Who would have guessed? The reason this is problematic is that there is no way in the current implementations of sql to express n-value logic results. Comparing null to 7/31/2010 should result in null or unknown (or something else depending how many values are in your logic table). The If construct in SQL is boolean, but the test I showed above is not.