C/ODBC and Date problems

I happened to find what seems to be a bug in C/ODBC date handling. I setup a query similar to this: SELECT Quantity, Shipment_Date FROM Sales_Line WHERE Shipment_Date >= {d’2002-05-01’} AND Shipment_Date < {d’2003-09-01’} For some strange reason I only got rows up through Jan. 8 2003. I thought that was kind of odd, since I was saying less than 09-01, maybe it was mixing the month and day up. So I played around with some more numbers, and a Shipment_Date < {d’2003-03-02’} gave me rows up to Feb 2nd. A Shipment_Date < {d’2003-13-01’} made the query fail, so it isn’t a complete day/month switch. To workaround my problem, I changed my application to use <= last day of the month. And that works fine. Like this: SELECT Quantity, Shipment_Date FROM Sales_Line WHERE Shipment_Date >= {d’2002-05-01’} AND Shipment_Date <= {d’2003-08-31’} I tested using MSQuery for all these, and I am running C/ODBC from Nav2.6f (2.60.06.7896). My server is Nav2.60. Objects are 2.60f w/ Manufacturing. In my ODBC setup, I’m NOT using “Closing Date Supported”, and my “Identifiers” are set to "a-z,A-Z,0-9,". Don’t know if this is a known issue, but I am curious to see if anybody else has ran into it before. -Todd PS - Here is the exact query my application is running (after the modification): SELECT Style, Color, Outstanding_Quantity, Outstanding_Amount, Quantity, Blanket_Qty__Taken, Amount, Project_Code, Document_Type, Shipment_Date, Sell_to_Customer_No FROM Sales_Line WHERE (((Document_Type = ‘Order’) OR (Document_Type = ‘Blanket Order’)) AND (Sell_to_Customer_No_ != ‘99025’) AND (Type = ‘Item’) AND (Outstanding_Quantity <> 0) AND Blanket_Order_No_ = ‘’ AND (Shipment_Date >= {d’2002-05-01’}) AND (Shipment_Date <= {d’2003-08-31’}))

I have seen this with some tables, but not all. I am too using 2.60F. I found that changing “Closing Date Supported” has some effect. You will have to use the other date format: MyDate >= {ts ‘2002-04-31 00:00:00’}. This is not in the C/ODBC PDF manual, but C/ODBC accepts it. This format is accepted only if “Closing Date Supported” in a certain setting, I don’t remember which. I tested the above format with ADO, code only connection.

I remeber that in this bug; the date format depends on the operator(>,<,>=,<=,=). Sometimes it’s yyyy-mm-dd and sometimes it’s yyyy-dd-mm. You can either try changing the “Closed Date Supported”, or use ADO’s rs.filter = “MyDate >= #4/31/02#” property to filter by date. This is clearly a bug.