Re: "Blank" date or datetime in apps (5 to 7 migrarton experience)

We faced the same problem a few years back. As I recall, when v5 compared (expression of type date) to (expression of type string), the common type was (string), so it could correctly evaluate expressions like date_field != " ". The way v7 evaluates these comparisons is to use the common type of (date), so it tries to convert " " to a date. When this FAILS, instead of issuing an error (-1213 or thereabout), it treats it as a successful conversion with value NULL. At this point, the clause is effectively of the form date_field != NULL, which is always false, since the only way to test for nulls is to use either "is null" or "is not null". We wound up going through all of our code, and fixing it, because there was no apparent quick fix or workaround. Good luck. Paul. <plkapp@erols.com> Boris Niyazov wrote: > > Migration from 5 to 7 brought me to another problem. Users reported me > of a starnge behavior like they are not getting propper reports as they > used to get before the migration. After my investigation I found the > following. > Some of applications that were written for the old engine used queries > based on retrieving records with not null date/datetime fields. Instead > of checking those fields just for null values, application programmer > used statements like that: > > select whatever from tab > where date_field is not null > and date_field != " "> > With version 4.x, 5.x it worked fine, but after the migration to 7 such > statements return no records. This creates a big headache to me. > I understand that this is a bad programming, but I had to live with that > since I inherited the applications. Now I am facing a magor problem of > tracking such statements and rewrite them. > > Anyway, what I am interested is what space character " " means for > date/datetime field in 7.x. If I run the statement > > select * from tab > where date_field != " "> > I get no rows found ! > -- > ======================================================================= > Boris Niyazov Ph: 212-854-4094 > IT Department Fax: 212-316-2623 > Columbia Law School Email: ban5@columbia.edu