Is Null Greater Than Any Date Data Type?

sql null db2
John Isaiah Carmona picture John Isaiah Carmona · Mar 9, 2012 · Viewed 37.8k times · Source

I have this query in DB2

SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A

If the SYSDATE is NULL, would it be greater than any value of @A, assuming that @A and SOMESCHEMA.SOMETABLE.SYSDATE is a Date data type?

Please help. Thanks in advance.

Answer

Eggi picture Eggi · Mar 9, 2012

Another predicate that is useful for comparing values that can contain the NULL value is the DISTINCT predicate. Comparing two columns using a normal equal comparison (COL1 = COL2) will be true if both columns contain an equal non-null value. If both columns are null, the result will be false because null is never equal to any other value, not even another null value. Using the DISTINCT predicate, null values are considered equal. So COL1 is NOT DISTINCT from COL2 will be true if both columns contain an equal non-null value and also when both columns are the null value.

DB2 Null Handling

That means that all comparison operations will be false because you are comparing an unknown value to something. So no matter which comparison you use (only the IS NULL/IS NOT NULL operation do work!), it will be false.

If you want the query to work you should use something like

SELECT * 
  FROM SOMESCHEMA.SOMETABLE 
 WHERE COALESCE(SYSDATE, TIMESTAMP_FORMAT('0001-01-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))  > @A

Related questions

IsNull function in DB2 SQL?

Is there a performant equivalent to the isnull function for DB2? Imagine some of our products are internal, so they don't have names: Select product.id, isnull(product.name, "Internal) From product Might return: 1 Socks 2 Shoes 3 Internal 4 Pants

Read More
db2 query condition where column not in () while the column value is null

Good day, I have a row of data with a column, call status, the value inside is null. If I query select * from table where status not in ('EXP'), before I run this query, I thought I can select the …

Read More
How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More