Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to select records w/ null date fields

How to select records w/ null date fields

How to select records w/ null date fields

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?
Replies continue below

Recommended for you

RE: How to select records w/ null date fields

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.  

Certified Pervasive Developer
Certified Pervasive Technician

RE: How to select records w/ null date fields

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close