ODBC Invalid datetime format. (#0) error after upgrading to Office 2016

user_8644294 0
Viewed 666

A little bit of background:

I'm migrating ton of Access 2000 .mdb applications from an old Windows 2003 server to a Windows 2016 Server that has Office 2016. The backend databases for them are also upgraded. All these applications are using Access as the frontend and Oracle as the backend.

Server Environment MS Access frontend Oracle Database backend Oracle Client References in the MS Access
Old 2003 Server Office 2000 Oracle 10g 10.2.0.4.0 10.02.00.01 enter image description here
New 2016 Server Office 2016 Oracle 19c 19.0.0.0.0 12.02.00.01 enter image description here

Most applications are working fine but few ones that involve datetime in their queries seem to fail with this error:

ODBC -- call failed.
[Oracle][ODBC] Invalid datetime format. (#0)

Note: There's no such issue while running them from the old 2003 server.

This is the VBA code snippet in the access (.mdb) file calling some query that's in it:

stDocName = "the_query_name"
DoCmd.OpenQuery stDocName, acNormal, acEdit 'acNormal and acEdit have values 0 and 1 respectively.

The the_query_name query:

--INVENTORY and PRODUCT_TYPE are tables in SOME schema in Oracle
--YING DATE TABLE is a table in MS Access that stores dates to use while querying the Oracle database.

SELECT SOME_INVENTORY.INVENTORY_DATE, SOME_PRODUCT_TYPE.PRODUCT_TYPE_CODE
FROM 
SOME_INVENTORY INNER JOIN SOME_PRODUCT_TYPE ON SOME_INVENTORY.PRODUCT_TYPE_ID = SOME_PRODUCT_TYPE.PRODUCT_TYPE_ID
INNER JOIN [YING DATE TABLE] ON SOME_INVENTORY.INVENTORY_DATE = [YING DATE TABLE].[Open Inventory Datetime]

-- Other Joins here
-- WHERE Clause here
-- ORDER BY here

My guess is that it's failing while trying to join the date in MS Access table to date in Oracle table. But why?

The YING DATE TABLE access table:

Inventory Date Inventory Time Inventory Datetime Open Date Open Time Open Datetime Close Date Close Time Close Datetime Open Inventory Datetime
7/7/2021 5:00:00 AM 7/7/2021 5:00:00 AM 7/1/2021 12:01:00 AM 7/1/2021 12:01:00 AM 7/7/2021 12:01:00 AM 7/7/2021 12:01:00 AM 7/1/2021 5:00:00 AM

The datatypes of dates that are joined:

Column Type
'SOME_INVENTORY.INVENTORY_DATE' column in Oracle DATE
'[YING DATE TABLE].[Open Inventory Datetime]' column in Access Date/Time
'SOME_INVENTORY.INVENTORY_DATE' column in Linked Access Table Date/Time

What I have tried so far:

  1. To see if the new 19c database or the new Oracle client has anything to do with it, I used the old oracle client 10.02.00.04 and the old database: Oracle 10g 10.2.0.4.0 while running the app from Office 2016. The error still doesn't go away. (Running it in old 2003 server from Office 2000 doesn't throw this error.)
  2. Upgraded the .mdb file by saving it as new database format of .accdb and ran it. The error still doesn't go away.

Can someone please help me with this? I've spent days on this already.

Thank You!

ms-access vba datetime oracle sql
2 Answers

Try with the correct syntax for date/time expressions in Access SQL:

SELECT 
    SOME.INVENTORY.INVENTORY_DATE, SOME.PRODUCT_TYPE.PRODUCT_TYPE_CODE
FROM 
    SOME.INVENTORY 
INNER JOIN 
    SOME.PRODUCT_TYPE 
    ON SOME.INVENTORY.PRODUCT_TYPE_ID = SOME.PRODUCT_TYPE.PRODUCT_TYPE_ID
WHERE
    SOME.TANK_INVENTORY.TANK_INVENTORY_DATE = #2021/07/01 05:00:00#
user_35272970

After spending almost a week on this, I finally found the issue and solved this problem.

It had to do with corrupted DATE values in the Oracle table. Some co-worker suggested to check Min and Max date values in that table and that's how I located the invalid date.

Select TO_CHAR(Min(INVENTORY_DATE), 'MM/DD/YYYY HH:MI:SS AM') "Min Date"
FROM SOME.INVENTORY;

1

After that, it was just a matter of locating the rows with the corrupted dates and either deleting them or updating them to a valid date. Look at this answer for locating and updating such rows:

Select and update rows with Date of 00/00/0000 00:00:00 00 in Oracle

user_86442940
Related