How do you pass MATLAB date (or user input) to SQL query for execution

>> myDate= datetime('2020-01-01');

>> selectquery = 'SELECT * FROM myTABLE WHERE reportDate >= '''myDate''' AND portfolio =''ABC'' ';

>> Data = select(conn,selectquery);

I have tried a few quotation mark sequences around 'myDate' but doesn’t seem to be working. My database is MS SQL Server 2013. The Query works if I specify a string date i.e. '2020-01-01' .

Your help is appreciated.

Answer 1

[Jakob B. Nielsen]

I remember struggling alot with this when I first started out grabbing data from SQL servers. I found that an approach where I constructed a series of strings in the following manner worked for me, maybe it will for you as well. For my server, the datetime format is yyyy-mm-dd. I feel like it "should be" easier than this, but once I found out that this worked I just went with it ;)

space={' '}; %for some reason, my query wont fire if there isnt a cell space string inserted. I actually dont even know why :)

selectquery=strcat('SELECT *',space);
connquery=strcat('FROM myTABLE ',space);
datestart=strcat('WHERE reportDate > ''2019-10-01 00:00:00.000'' ',space);
dateend=strcat('AND reportDate < ''2019-12-31 00:00:00.000'' ',space);

Data=select(conn,[selectquery{:} ...
    connquery{:}
    datestart{:}
    dateend{:}]);

[ARS]

Hi, Thanks for your answer but this one didn't work for me.

I used the following and it works:

selectquery = 'SELECT *  FROM myTable WHERE myDate BETWEEN ''%s'' AND ''%s'' ';
myQuery = sprintf(selectquery, myDateOld, myDateNew);
myData = select(conn,myQuery);

This works great.

Answer 2

[ARS]

Hi, Thanks for your answer but this one didn't work for me.

I used the following and it works:

selectquery = 'SELECT *  FROM myTable WHERE myDate BETWEEN ''%s'' AND ''%s'' ';
myQuery = sprintf(selectquery, myDateOld, myDateNew);
myData = select(conn,myQuery);

This works great.