Comparison with NULL value and date in 4gl

Question

I have a value comming from database which is a date in informix.Sometimes this value will be null or else it will be a date.I am comparing that value with todays date like the following.

if(value_from_db <= todays_date){
  //display Todays greater
}
else{
  //Display Todays smaller
}

What will be the output here.It will print Todays greater or Todays smaller My question is that if value_from_db is NULL will it goes inside the if condition or inside the else condition.I found something in this which is described as

A date field gets NULL value. If I use a FOR EACH even less or greather than a user date this NULL field record appear. It seems to be NULL greather than all other dates and at same time lees than all other dates.

If the field which stores NULL value is an index component NULL values are sorted high.

I need to convert this into php but after analysing the correct o/p.In case of php

if(strtotime(NULL) <= time())
  echo 'Todays greater';
else
  echo 'Today is smaller';

This will ouput

Todays greater

I am confused.Any help is much appreciated.

I already see different situations where NULL value have different treatment, so you can never trust NULL value. The correct way to work with it is check it explicit or put some constraint to guarantee you never receive one. At the database AND 4GL the correct statement to check NULL is : ` IS NULL`. **IMPORTANT** never use the equal ` = NULL` this not work! For PHP you have the [is_null()](http://www.php.net/manual/en/function.is-null.php) function — ceinmart, Nov 09 '13 at 00:27

score 1 · Accepted Answer · edited May 23 '17 at 10:24

It will enter ELSE either you do <= todays_date or >= todays_date, since NULL is neither bigger or smaller.

You should have something on the query that fuels that value_from_db to not return you NULL values OR validate specifically for them in the IF.

Here's a procedure to test that:

CREATE TABLE datet(date1 DATE);
INSERT INTO datet VALUES(null);

CREATE PROCEDURE validateNullDate()
    RETURNING char(5)

    DEFINE dateVar DATE;

    SELECT date1 INTO dateVar FROM datet;

    IF dateVar <= TODAY THEN
        RETURN "if";
    ELSE
        RETURN "else";
    END IF
END PROCEDURE;

Even changing the IF to: IF dateVar >= TODAY THEN. You'll always get "else".

EDIT:

About your question of getting strtotime(NULL) smaller than today, you can find a good explanation on this answer:

NULL is interpreted as 0 by strtotime, since it want to be passed an integer timestamp. A timestamp of 0 means 1-1-1970.

So it will go in the if.

Thanks for your answer.So you are saying that unless `value_from_db` contains a date it will always enter into the else case.? — 웃웃웃웃웃, Nov 08 '13 at 12:29
Yes. If you want it to enter the IF, you should add a validation for nulls in the IF. — Filipe Silva, Nov 08 '13 at 12:30

score 1 · Answer 2 · answered Nov 08 '13 at 12:28

1

NULLs (or ?) are strange creatures in the ABL - the best way to deal with them is to test explicitly for them like so:

IF value_from_db <> ? AND value_from_db > some-date THEN 
    /* do something */ 
ELSE
    /* DO something else. */

answered Nov 08 '13 at 12:28

Tim Kuehn

3,201
1
17
23

score 0 · Answer 3 · answered Nov 10 '13 at 22:34

Simply put, any arithmetic comparison where any of the variables involved are NULL will result in false, which means your code in its current form drops through to the ELSE clause.

You need to test explicitly for NULL, e.g.

IF value_from_db IS NULL THEN
    /* display date is unknown */
ELSIF value_from_db <= TODAY THEN
    /* display date is in the past */
ELSE
    /* display date is in the future */
END IF;

The order of testing is not terribly important in your example. You could also write this as

IF value_from_db <= TODAY THEN
    /* display date is in the past */
ELSIF value_from_db > TODAY THEN
    /* display date is in the future */
ELSE
    /* display date must be null */
END IF

Comparison with NULL value and date in 4gl

3 Answers3