MATLAB: Select Query with dynamic dates

select dates gui

Hello

I am trying to execute a simple select query from my SQL database( dexplore on Matlab). I input the date in a gui, which takes in the date format with this command :

SDV = sprintf('%04d-%02d-%02d',SYYYY,SMM, SDD)

I get a date like this 2XXX – MM – DD

Then I need to import some data with date > SDV

My query is the following :

conn = database('', '', '', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'TW\SQLEXPRESS', 'PortNumber', 1433, 'AuthType', 'Windows');
%Read data from database.

curs = exec(conn, ['SELECT   XIV.Date'...
    ' ,  XIV.Value'...
    ' FROM   master.dbo.XIV '...
    ' WHERE   XIV.Date >='  SDV  ]);

curs = fetch(curs);

The Query dont work because its not accepting the syntax at the end. When I use a fixed date, the syntax it takes is the following :

    conn = database('', '', '', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'TW\SQLEXPRESS', 'PortNumber', 1433, 'AuthType', 'Windows');
    %Read data from database.
    curs = exec(conn, ['SELECT   VXX.Date'...
        ' FROM   master.dbo.VXX '...
        ' WHERE   VXX.Date > ''2004-05-20''']);
    curs = fetch(curs);
  close(curs);

This works but the date is not dynamic here.

Do you know how to write the query correctly in order for it to accept the SDV date?

Thank you very much

D

Best Answer

In place of

' WHERE   VXX.Date > ''2004-05-20''']);

try

' WHERE   VXX.Date > ''',SDV,'''']);

The problem with your original syntax is that it was failing to put in the required enclosing single quotes.

Related Solutions

MATLAB: How to generate an SQL query that contains dates with Database Toolbox

Different databases require you to specify dates in different formats. Some databases accept dates in a standard string format ('19-Jan-2002') while others require special characters in the string to identify the string as a date.

In general, consult your database documentation for the proper format for dates in SQL queries. Below is some specific information for a couple of databases:

Microsoft Access:

With Microsoft Access, use a # character before and after the date string. Below is an example, where "conn" is a database object in MATLAB as returned by the DATABASE command. The table name is "myDates", and the column name is "DateJoined". In order to select all records where the DateJoined field is after 3/5/05, use the following:

curs = exec(conn,'select * from myDates where DateJoined > #03/05/2005#');
d = fetch(curs);
data = d.Data

Oracle:

With Oracle databases, the date string must be preceded by a data type identifier and enclosed in brackets. Below is an example, where "conn" is a database object in MATLAB as returned by the DATABASE command. The table name is "myDates", and the column name is "DateJoined". In order to select all records where the DateJoined field is after 3/5/05, use one of the following examples. The first statement uses the TIMESTAMP data type:

curs = exec(conn,'select * from myDates where DateJoined > {ts ''2005-03-05 00:00:00''}');
d = fetch(curs);
data = d.Data

The second statement uses the DATE data type:

curs = exec(conn,'select * from myDates where DateJoined > {d ''2005-03-05''}');
d = fetch(curs);
data = d.Data

MATLAB: JDBC Database connection through command failing.

I found a workaround issue which might help someone if ever one has the same issue, I connected to dexplore, connected to my database, I selected any table and imported the table by selecting Generating Script. And it gives you the syntax to connect to the database which is

conn = database('', '', '', 'Vendor', 'MICROSOFT SQL SERVER', 'Server', 'TW\SQLEXPRESS', 'PortNumber', 1433, 'AuthType', 'Windows');

And this works.

Related Question