Link to home
Get AccessLog in
Avatar of jamessa
jamessa

asked on

Using DateField is null in Pervasive SQL v10

I wrote a SQL statement for Pervasive SQL v10 using in the "Where" clause "where Delete_Date is Null"

This worked fine in Pervasive SQL v8, but it doesn't work in Pervasive v10.  How do I need to code this in order to query for null values.  I'm using ODBC because it works quicker than OLE DB when querying Pervasive.

Sincerely,
Bruce Edgar
email: bedgar@aeshr.com
Avatar of dorianm
dorianm

You have to check that you were not assuming that 00:00:00 is equal to NULL. It should not be the case, and if you were using this property, you were using a bug in older version of PSQL.

http://www.pervasive.com/kb/kb.asp?fx=getdetails&solid=00016417&title=Time%20values%20with%20a%2000:00:00%20default%20value%20becomes%20NULL%20when%20an%20INSERT%20is%20performed

Try this :


WHERE
        Delete_Date = '00:00:00'
     OR Delete_Date = NULL

Open in new window

Avatar of jamessa

ASKER

Some more information that might help.  This is an older type program that dealt with btrieve.

My Delete_date is null doesn't error out, it just doesn't return any of the records with the Delete_Date that is null, should be about 313 records.
Avatar of jamessa

ASKER

Re-Posting:
Apparently my 1st comments didn't post correctly.

Thank you, Dorianm, for the suggestion.  
I tried both, got "Incompatible Types in predicate" for Delete_Date = null, and
"Invalid Date, Time, or Timestamp" for Delete_Date = '00:00:00'
ASKER CERTIFIED SOLUTION
Avatar of jamessa
jamessa

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access