How to select records w/ null date fields

[ProtocolPirate]

I tried doing a "SELECT IFNULL(DateField, '1111-1-1')" as well as "SELECT IFNULL(DateField, CAST('1111-1-1' AS DATE))" but I always get the following errors:

[ODBC Engine Interface]Error in row.
[ODBC Engine Interface]Invalid date, time or timestamp value.
[ODBC Engine Interface]Expression evaluation error.

How can I format a date field so that my VB.Net program doesn't blow up on null date fields?

 

[mirtheil]

A few questions:
- What version of PSQL are you using?
- What interface (ODBC, OLEDB, PSQL Provider) are you using in VB.NET?
- What exactly is in the field? For example, is it really a "Null" value?

A comment or two:
- VB.NET can handle Null values. You would need to test for Null in your application before displaying it or manipulating it.
- VB.NET (or any ODBC/OLEDB/PSQL Provider application) cannot handle invalid dates. It sounds like your query isn't really returning null but is returning an invalid date. An valid date is any data that has a month of 1 to 12, day of 1 to 31 dependent on the month (no February 30th), and a year 0000 to 9999. My guess is that your data is actually returning a date of 00/00/0000 which is invalid.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ProtocolPirate]

I'm using the Provider for version 9.5. The value of the field displays as null in an ODBC test utility, however, I can't do a SELECT * WHERE IsNull(DateField). I get no records, but if I SELECT a record with a null date the utility prints <null> for that field.

I do have a check in my code to see if a value that was read in from PSQL is null, but it never gets to that point in the code. VB.NET is throwing an exception on the Read() method, not at some later point.

The errors I mentioned before are from an extremely simple SQL query test utility, such simple statements as SELECT IFNULL(DateField, '1111-1-1') fail both in VB.NET and in the simple SQL utility if the date field is null.

I've never had a problem with the IFNULL function before, it works for me with every other field type, but it appears that it does not operate in a consistent fashion for date fields.