Database Toolbox select a certain date error datatypeconflict criteriastatement

Dear all who it may concern!

I created an SQL-Statement which selects an ID from a certain date. The SQL-Statement is pretty much this Format (The Original is much longer, yet the error message can not be due a spelling mistake!):

SELECT Table.ID_Table FROM Table WHERE Table.Date = '2017-05-02 00:00:00.0';

I get an error that states: (to be read in German accent!) Invalid Cursor: [Microsoft][ODBC-Treiber für Microsoft Access] Datentypenkonflikt in Kriterienausdruck.

(to be read in propper english accent) Invalid Cursor: [Microsoft] [ODBC-Driver for Microsoft Access] Datatypeconflict in criteriastatement.

When I try to make a selection with the 'MATLAB Database-Browser' i get the same error message! It is unusual, that ín the browser i get a different dateformat than in Access. In Access the format is 'DD.MM.YYYY' while in the browser I get 'YYYY-MM-DD HH:MM:SS.S'. I couldnt figure out how to Change it in MATLAB, therefore i changed the dateformat in Access to the same in MATLAB. Changing the Prefered date and time Format in MATLAB had not the wanted effect. I allready tried to convert the date in the statement with str_to_date, CONVERT, to_date etc.. Yet i allways get an error that it doesnt recognize These functions....

My thought is, that MATLAB doesnt recognize that I want to give him a date.

My MATLAB Version is:

>> ver ---------------------------------------------------------------------------------------------------- MATLAB Version: 9.1.0.441655 (R2016b) MATLAB License Number: 966221 Operating System: Microsoft Windows 10 Pro Version 10.0 (Build 14393) Java Version: Java 1.7.0_60-b19 with Oracle Corporation Java HotSpot™ 64-Bit Server VM mixed mode ---------------------------------------------------------------------------------------------------- MATLAB Version 9.1 (R2016b) Database Toolbox Version 7.0 (R2016b) MATLAB Compiler Version 6.3 (R2016b) Signal Processing Toolbox Version 7.3 (R2016b)

Answer 1

[Guillaume]

Note: I know nothing about the database toolbox as I don't have it and have never seen a need for it to communicate with access databases (I use basic actxserver and write my own queries).

But clearly, the syntax of your query is not valid for Access SQL. Dates must be enclosed in #, not ' and must be in US format regardless of the database locale as explained on this page, so your query should be:

SELECT ID_Table FROM Table WHERE Table.Date = #05/02/2017 00:00:00#

At least, that works for me on a test database:

connection = actxserver('ADODB.connection');
connection.Provider = 'Microsoft.ACE.OLEDB.12.0'
connection.Open('C:\Test\TestDb.accdb')
cursor = connection.Execute('SELECT * FROM Table1 WHERE Table1.Field2 = #05/02/2017 00:00:00#')
while ~cursor.EOF
   fprintf('%d\t%d\t%s\n', cursor.Fields.Item(0).Value, cursor.Fields.Item(1).Value, cursor.Fields.Item(2).Value);
   cursor.MoveNext
end

prints the correct record(s):

5  0  02/05/2017
7  2  02/05/2017

Answer 2

[Mobby]

Terrific! Thats the answer. I tried the # but not with that timeformat ;) Thank you very much!