DateTimes Through OLEDB in VFP

For some time, we've been running queries against a SQL Server database through an OLE DB connection. Recently, I came across some strange behaviour when retrieving datetime values.

In one location, the application populating the SQL database allows for an empty date value. However, SQL Server doesn't allow for an empty datetime value (though it does support a null datetime). The application handles this by using the maximum SQL Server datetime value of 9999-12-31 23:59:59.997 to represent an empty date. The data looks something like this:

This is maybe a bit unusual, but not too strange. However, when querying this data through an OLE DB connection in VFP, this is the result:

That's curious. Why is the date appearing as a blank? Doing some quick investigating on the retrieved record, both Empty(ONDATE) and IsNull(ONDATE) return false. Even more curious. What would make a datetime value display as blank, but still evaluate as not empty and not null?

Ok, let's try connecting to the database using an ODBC connection instead. Here are the results of the same query:

Curious again. The data is appearing correctly here. Something must be different in the way that OLE DB and ODBC are handling these datetime values. Let's add a couple more testing values, and then retrieve them both ways, to see if that sheds any light on the situation.

This shows what's going on. The OLE DB connection is using the milliseconds to round the value to the nearest second, while the ODBC connection is just truncating the milliseconds. The weirdness with the blank value is coming from the fact that 9999-12-31 23:59:59 is also VFP's maximum datetime value, and the rounding is forcing the value past this maximum.

I generated a VFP table using the results of the OLE DB query, and opened it with a hex editor. Sure enough, there is data in the "blank" datetime value. VFP stores a datetime value in two pieces: the date as a Julian day, and the number of milliseconds past midnight. The problem value has a number of milliseconds that evaluates to slightly more than 24 hours. The Empty and IsNull functions are correctly reporting false, based on the fact that there really is a value stored there.

All in all, this is not too much trouble to work around, since we can just use an expression with CASE or DATEADD/DATEPART to have SQL Server adjust the value for us before sending the query results. It's good to understand this behaviour though, since it will also appear in other situations where milliseconds are included in datetime values, and it will be much less noticeable that any rounding is happening.

Pumpkin Carving Madness

For the past few years, we've held a pumpkin carving contest for Halloween. People come over and carve their pumpkins, and then the trick-or-treating kids & parents vote for their favourite. I won the first contest in 2006, but I haven't been able to repeat. Kathleen has won the past two contests.

Here are a few pictures from this year.

Pumpkin Carving Madness 2009 underway

Owen inspecting one of the pumpkins

My entry for this year, good for second place in the voting

Kathleen's winning entry

Owen at 6 Months

Here are some new photos of Owen. He's six months old now!

SQL Subqueries with Null Values

Null values in data can often cause unexpected results. Recently I came across a case where the field in a NOT IN subquery contained null values, and I didn't get the behaviour I was expecting. After spending some time with this, I have it worked out, and the behaviour does make sense. It's worth having a look at.

We commonly use a NOT IN subquery to retrieve records that do not have a related record in another table. The common example of this type of query is to retrieve all customers that do not have any orders. Here's a simple example of a Customers table and an Orders table:

Customers                Orders
CustID    FullName       OrderID   CustID    OrderDate
     1    John Doe             1        2    1/1/2009
     2    Jane Doe             2        3    1/2/2009
     3    Jack Smith           3        3    1/3/2009
     4    Jane Smith

There are four customers, and two of these customers have placed orders. We can use IN and NOT IN subqueries like these:

select * from Customers where CustID in (select CustID from Orders)
2 Jane Doe
3 Jack Smith

select * from Customers where CustID not in (select CustID from Orders)
1 John Doe
4 Jane Smith

This is as we would expect. However, let's add a fourth record to the Orders table, with a null value for the CustID:

OrderID   CustID    OrderDate
      4     null    1/4/2009

Now, when we run the IN query, the results are unchanged; we still get customers 2 and 3. However, when we run the NOT IN query:

select * from Customers where CustID not in (select CustID from Orders)
No records returned

Why are there no records returned? Shouldn't we still be getting customers 1 and 4, since these CustIDs do not appear in the Orders table? Well, let's look at how this gets handled. The subquery generates a list of CustIDs, like this:

select * from Customers where CustID not in ( 2, 3, null )

Logically, the NOT IN is treated as a series of not equals expressions, like this:

select * from Customers where ( CustID <> 2 and CustID <> 3 and CustID <> null )

Now, we can consider how this evaluates for our customer records. For customer 1, the where clause becomes:

1 <> 2 and 1 <> 3 and 1 <> null
= true and true and null
= null

This is why record 1 doesn't appear in the result set; for records to appear, the where clause must evaluate to true, not to null.

Now that I've gone through the logic on this, it's not really correct to say that customers 1 and 4 don't have any orders. The null CustID value in the Orders table means that the customer for that order is unknown, so we can't guarantee that this order doesn't belong to customer 1 or 4.

Stonefield Article on SoftwareCEO

Stonefield Software is the subject of an extensive article on SoftwareCEO.

Stonefield Profile in the Financial Post

The Financial Post published an article profiling Stonefield Software yesterday.

Prairie firm finds its markets

Stonefield Profile

An article profiling Stonefield Software, the company I work for, was on the front page of the business section in the Leader Post. The article is available on the Leader Post website.

Stonefield Software the little Regina company that could