Select Query with dynamic dates

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

Answer 1

[the cyclist]

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.

[Davin]

Thanks 'the cyclist'. It works perfectly well.

[the cyclist]

I often write out the actual text like this first

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

then break the line up into the different strings as an intermediate step (to ensure I am getting the quotes right)

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

then finally replace the string with the variable

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

That intermediate step always involves putting in a pair of QUOTE-COMMA-QUOTE to set off the string you want.

[Davin]

Yes thats a good technique. Because quite often, I have this issue on the syntax, I was trying stuff like [ { } ] but it didn't work. Then tried to replicate with the fixed date but it didnt work too, because it was not taking the value of SDV but as string of characters... thanks again

[the cyclist]

Sorry to be commenting in drips and drabs, but another trick I do is to assign the query to a string variable:

queryText = 'SELECT * FROM TABLE'

Then after I build the query (possibly replacing with a variable, as you have done), then I'll print queryText to the MATLAB screen, just to make sure it is the exact string that needs to be sent in SQL.

[Davin]

yeah good technique too. In fact, to know the syntax, i went on the database explorer, on the small sql query on top, i put the fixed date, normally if your syntax is good here, for example for dates, you need to put something like '2004-04-01' then it will filter in yr table.You have an import button, with a down button, u click on generate script... MATLAB writes you the query for you, it can put on the correct path when it comes to writing the query directly. But as i was using SDV it was a bit more tricky. No issues at all for commenting. Sharing experiences is very important...