not null condition check

Question

In Informix:

how to check for not null,

I have a field with datetime data type, and I don't have any default values for this field, but I have lot of empty/null records for this field.

I want a query that will render the records:

(  cond like )  where  my_curr_date_time != "" 

but if I try this query, it is not working. Please tell which query for finding not null records.

Solution

... WHERE my_curr_date_time IS NOT NULL

NULL is a special value. It may mean "no value" or "value unknown". In SQL if you want to check if value is or not is NULL then you must use ... yes ... value IS NULL or value IS NOT NULL. It may be used with other conditions like:

... WHERE my_curr_date_time IS NOT NULL AND my_curr_date_time > start_dt

Comments

[Jonathan Leffler]

Except that the DATETIME type is peculiar to Informix, this answer applies to most variants of SQL and most data types.