Subject Re: [firebird-support] NULL Date values
Author Paul Vinkenoog
Helo Jeff,

> I have experience with non-SQL databases. I am currently working on
> a web application and have run into a problem that happens when the
> user does not supply a date and NULL is stored to FB.

> Last night, I installed rFunc and used DateToStr which properly
> converts the date value to a string format, but if the date value is
> NULL, the string value returned is 11.17.4294967254!

Firebird's "zero date" (not the null date!) is 17 November 1858. That
accounts for the 11.17 in the string.

4294967254 is the unsigned 32-bite representation of -42 (the number
of years that 1858 lies before 1900).

> I don't know what that value is supposed to mean and I am surprised
> that the DateToStr function does not test for NULL and return an
> empty string whenever a NULL date is recieved.

I presume that you are referring to the Delphi/BCB DateToStr function,
which expects a TDateTime argument. Since TDateTime is a float, it can
never be NULL, only 0. You have "lost" the NULL earlier, when the
database field was converted to a TDateTime.

You have to test if the field is NULL before you assign the value to a
datatype that can't hold NULLs. How you must do this depends on your
connectivity components. With Borlands TField and IBO's TIB_Column,
use the IsNull property.

> This leads me to believe that maybe I should be storing an empty
> value into a date field when the date is unknown. Is there a way to
> store an 'empty' date value in FB2 instead of NULL?

No, you have to store a valid date or NULL (= "unknown"). Storing an
"empty" date, if it were possible, wouldn't help you anyway.

Kind regards,
Paul Vinkenoog